2nd condition in a conditional formatting not working

[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.

 

[Dave Peterson]

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.

 

[Kurt Heisler]

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.

 

[Claus Busch]

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.

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.

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.

Repeat procedure above for Column B



Mit freundlichen Grüssen
Claus Busch

 

[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

 

[Dave Peterson]

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.

 

[Dave Peterson]

And the addresses of the cells with the conditional formatting.

 

[Kurt Heisler]

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

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

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

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.

 

[Claus Busch]

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.

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

 

[Kurt]

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.  

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.

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.

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.

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

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