Conditional format - blank vs. 0
 

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

Conditional format - blank vs. 0
 

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" wrote:

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

Conditional format - blank vs. 0
 

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


Gord Dibben MS Excel MVP


On Thu, 7 Feb 2008 20:31:01 -0800, NB wrote:

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

Conditional format - blank vs. 0
 

It worked! Thanks so much. NB

"akphidelt" wrote:

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" wrote:

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

Conditional format - blank vs. 0
 

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

"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP


On Thu, 7 Feb 2008 20:31:01 -0800, NB wrote:

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

Conditional format - blank vs. 0
 

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


Gord

On Thu, 7 Feb 2008 20:56:00 -0800, NB wrote:

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

"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP


On Thu, 7 Feb 2008 20:31:01 -0800, NB wrote:

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