conditional formatting for blank cells

[Stig - tame racing driver]

Help

Using the conditional formatting tool is it possible to have a colour for
cells with 0 and a colour for blank cells

Thanking you

 

[Bernard Liengme]

I selected a range A1:C5; some of these cells were blank others had 0 in
them
In CF dialog I used these two formulas
=ISBLANK(A1)..... green fill
=AND(ISNUMBER(A1),A1=0)... red fill
This gave me the result you wished to have
Note: it is not sufficient to use =A1=0 since blank cells will satisfy this
test

 

[Mike H]

Hi,

Select the range of cells and this assumes A1 is the first cell then
format|Conditional format
Select 'Formula is' and enter the formula

=ISBLANK(A1)
Pick your colour

Click ADD and repeat the above using this formula

=AND(ISNUMBER(A1),A1=0)

Mike

 

[Kevin B]

Change the condition to Formula Is and use the following formula to determine
if a cell is blank:

=ISBLANK(A1)

Then select the formatting for that condition.

Add a second condition and use the following formula for 0 (zero) values:

=A1=0

Then select the formatting for that condition.

Change the cell address A1 accordingly.

 

[Stig - tame racing driver]

Thank you ........

I selected a range A1:C5; some of these cells were blank others had 0 in
them
In CF dialog I used these two formulas
=ISBLANK(A1)..... green fill
=AND(ISNUMBER(A1),A1=0)... red fill
This gave me the result you wished to have
Note: it is not sufficient to use =A1=0 since blank cells will satisfy
this test
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[Mike H]

Note: it is not sufficient to use =A1=0 since blank cells will satisfy this

test

I posted the same solution as yourself because I think it's good practice to
eliminate blanks evaluating as 0 but if you evaluate the isblank as the first
condition then =a1=0 is fine as the second

Mike