2nd condition in a conditional formatting not working

  • Thread starter Thread starter Kurt Heisler
  • Start date Start date
K

Kurt Heisler

I'm trying to set up two conditions for conditional formatting in my
Excel worksheet.

Condition 1 makes every other row (about 40 rows across 5 columns)
gray:

1. Formula Is =MOD(ROW(),2)=1 (and setting a gray pattern for the
format)

Condition 2 sets to red the font of values in Column E, if the value
is greater than a value in A1.

2. Cell Value Is greater than = $A1 (and setting the font to red)

Condition 1 always works, but condition 2 works only for rows (well,
actually just the cell in Column E) that are *not* gray. If the cell
happens to be one of the grayed ones, the font remains the same
regardless of the value.

Any idea what I can change? Thanks.
 
Just one more thing to worry about...

Did you want to always check against the value in A1 (no matter what row)?

If yes, then use $a$1 (not just $a1) in your formulas.
 
Adding those additional conditions, either with $a$1 or $a1, had no
affect. The alternate rows gray, but the red font isn't applied. This
is what I did:

1. I applied the 1st condition to every row across Columns A-E and set
the *pattern* formatting to gray (this worked).
2. I applied the 2nd and 3rd condition only to rows in Column A, and
set the *font* formatting to red (had no affect).

To give you more information, I have about 40 rows of data across 5
columns (A2:E40)

I would like every other row (in A2:E40) in gray.

For values in Column A (A2:A40), I would like the font to be red if
the value is > than the value in A1.

For values in Column B (B2:B40), I would like the font to be red if
the value is > than the value in B1.

And so on.

After my initial post, I read that you can't apply multiple conditions
via Format > Conditional formatting, because once the first condition
is satisfied it stops. (Therefore, perhaps visual basic is needed.) So
I was surprised to see a solution that proposed multiple conditions.
Perhaps I misinterpreted this?

Thanks.
 
Hallo Kurt,

Am Wed, 23 Jun 2010 06:50:03 -0700 (PDT) schrieb Kurt Heisler:
I would like every other row (in A2:E40) in gray.
Click to expand...

select A1:E40 => CF => formula:
=MOD(ROW(A1);2)=0
For values in Column A (A2:A40), I would like the font to be red if
the value is > than the value in A1.
Click to expand...

Select A2:A40 => CF => formula:
=A2>$A$1
For values in Column B (B2:B40), I would like the font to be red if
the value is > than the value in B1.
Click to expand...

Repeat procedure above for Column B



Mit freundlichen Grüssen
Claus Busch
 
Hallo Kurt,

for the 2. Condition:
Select A2:E40 => CF => Formula:
=A2>A$1 and font red


Mit freundlichen Grüssen
Claus Busch
 
If you still can't get it to work, you should share the exact formulas you used
and the version of excel you're using.
 
I would like every other row (in A2:E40) in gray.
select A1:E40 => CF => formula:
=MOD(ROW(A1);2)=0
Click to expand...

With the semicolon, I get, "The formula you typed contains an error."
I changed the ; to a , and it works.

The cell range is A1:E40 and the formula is =MOD(ROW(A1),2)=0.
for the 2. Condition:
Select A2:E40 => CF => Formula:
=A2>A$1 and font red
Click to expand...

This option applied the criteria but only to values in the non-grayed
rows. The grayed rows were skipped.

The cell range is A2:E40 and the formula is =A2>A$1.

You can open the sample Excel sheet with the formatting I'm using
here:

http://dl.dropbox.com/u/6817228/conditionalformat.xls

(And to further complicate things, I suspect if I want to reverse the
criteria in, for instance, Column B - so values that are *LESS* than
B1 become red - I'd need to change the range for condition 1 and add a
3rd condition to handle B separately.)

Thanks for your continued help.
 
Hi Kurt,

Am Wed, 23 Jun 2010 15:49:56 -0700 (PDT) schrieb Kurt Heisler:
This option applied the criteria but only to values in the non-grayed
rows. The grayed rows were skipped.
Click to expand...

I think, you want to change the pattern to red. If the first condition
is true, the second condition don't work. Try the font red as you read
in your OP. If you want to schange the Pattern in red, make this
condition first.


Regards
Claus Busch
 
Hi Kurt,

Am Wed, 23 Jun 2010 15:49:56 -0700 (PDT) schrieb Kurt Heisler:


I think, you want to change the pattern to red.  
Click to expand...

Not sure why choosing red for the pattern vs. gray for the pattern
would matter. Also, if the row pattern is red, and the font of the
value in that row is red, you won't be able to see the value!
If the first condition is true, the second conditiondon't work.
Click to expand...

That's what I thought, but the replies suggest there's a way around
this limitation.
Try the font red as you read in your OP.
Click to expand...

Not sure what you mean. I am trying to change the font to red; that's
the second condition:

.... > Select A2:E40 => CF => Formula:
.... > =A2>A$1 and font red
If you want to change the Pattern in red, make this condition first.
Click to expand...

My first condition is where I'm changing the pattern (to gray, not
red):

.... > select A1:E40 => CF => formula:
.... > =MOD(ROW(A1);2)=0
 
Back
Top