Conditional format - blank vs. 0

[NB]

How can I get conditional formatting to treat a blank cell differently than a
0 value? If it is 0, I want a red background. If it is blank, I don't want
any special formatting. I've tried everything I can think of and Excel treats
blank cells as if they are 0. Help! Thanks! NB

 

[akphidelt]

Alright, well there is a fix to this problem.

Here are the steps

1) Select the area in which you want the condition formatting
2) Select Condition Formatting
3) Change cell value is to formula is
4) Enter the formula =AND(ISNUMBER(A1),A1=0)
5) Then format the cell to the red background

Use A1 only if A1 is the beginning of the conditional formatting... if you
start the range at D3 change A1 to D3, etc.

Hope this helps

 

[Gord Dibben]

=AND(E6=0,(NOT(ISBLANK(E6))))


Gord Dibben MS Excel MVP

 

[NB]

It worked! Thanks so much. NB

Alright, well there is a fix to this problem.

Here are the steps

1) Select the area in which you want the condition formatting
2) Select Condition Formatting
3) Change cell value is to formula is
4) Enter the formula =AND(ISNUMBER(A1),A1=0)
5) Then format the cell to the red background

Use A1 only if A1 is the beginning of the conditional formatting... if you
start the range at D3 change A1 to D3, etc.

Hope this helps

 

[NB]

This one worked too. Thanks Gord. I sure appreciate the help from both you
and akphidelt. It was driving me nuts!

 

[Gord Dibben]

akphidelt's was a little neater and easier to enter but thanks for the feedback.


Gord