Excel - Conditional Formatting Cells With Values 0 and Null

Asked By RookieAccessUse on 06-Aug-09 04:25 PM
I want the background of a cell one color if it holds a value of 0, another
color if it is null or empty, and another color if the value is >0<11.  It
seems that Excel treats 0 and an empty cell the same.  Whatja think?

Bernard Liengme replied on 06-Aug-09 04:40 PM
These rules worked for me
for the blanks: =ISBLANK(A1)
for the zeros: =AND(ISNUMBER(A1),A1=0)
for the >0<11= =AND(A1>0,A1<11)
The 'trick' was ISNUMBER in the zero check! Otherwise it captures the blanks

best wishes
Rick Rothstein replied on 06-Aug-09 04:42 PM
You can do what you want using these 3 Conditional Format formulas (assuming
A1 is the active cell when you create them)...

Empty (Null) Condition:    =A1=""

Zero Condition:    =A1=0

Range Condition:    =AND(A1>0,A1<11)

Rick (MVP - Excel)