conditional formatting for blank cells

  • Thread starter Stig - tame racing driver
  • Start date

S

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
 
Ad

Advertisements

B

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
 
M

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
 
K

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

Stig - tame racing driver

Thank you ........

Bernard Liengme said:
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
 
Ad

Advertisements

M

Mike H

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


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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top