Conditional Formatting - Blank or Zero

[Annie]

Using Excel 2003, I have cells that may contain a zero, or may be blank. I
want conditional formatting to color only those cells that have a 0. I've
tried Allen Wyatt's tips on
http://exceltips.vitalnews.com/Page...rmats_that_Distinguish_Blanks_and_Zeroes.html
and nothing is working. Perhaps there is some option or add-on that needs to
be checked?

A zero should change color. Blanks, no change.
Thank you to anyone who may assist with this frustrating problem.
Cheers,
Annie

 

[Tyro]

Enter your formating with a formula such as:

=IF(ISBLANK(A1),FALSE,IF(A1=0,TRUE,FALSE))

Tryo

 

[Annie]

Cancel Request. I figured it out. You could not select the entire range of
cells to apply the conditional format to. Just format the first cell, then
right-click drag and copy format. Thanks anyway all. Leaving post in case
someone else can use it.

 

[Tyro]

In Excel 2007, you can apply your conditional formats to a multi-cell range.
Just select the range in the formatting dialogue.

Tyro

 

[David Biddulph]

Why did you use IF(A1=0,TRUE,FALSE) and not just A1=0 (which itself returns
TRUE or FALSE)?

You can get rid of the other IF too, and just have
=AND(A1=0,NOT(ISBLANK(A1))) or =AND(A1=0,A1<>"")

Perhaps, Tyro, you can explain why you've added the extra IF functions?

 

[David Biddulph]

I don't know why you say you can't select the whole range and apply the CF
to the whole lot at once.

 

[Tyro]

I was simply showing the OP the concept in the formula. You may reduce it to
your liking. Have fun.

Tyro

 

[Tyro]

Because when I first tried selecting the cells, the selection did not show
up in conditional formatting. When I tried the second time it did. Would you
please stop being a jerk?

Tyro

 

[Annie]

Thank you Tryo and David very much. I'm saving your formulas.

This is what I did in first cell.
Condition 1: Formula is =ISBLANK(A1) - No format set
Condition 2: Cell Value Is equal to 0 - Format pattern and chose color
Right-click, drag down, copy formats only.
David you are correct. I could have done the entire range. My error was
specifying A1 as blank, not the first actual cell address (which was G13) in
the range. Also, someone had changed the pattern color as a cell format -
took awhile to find that. Thanks all! Annie

 

[Gord Dibben]

Annie

You CAN select the entire range to format.

Just make sure Excel doesn't helpfully change the cell reference to Absolute by
adding $ signs.

i.e. =ISBLANK($A$2) is what Excel may change to.

You want =ISBLANK(A2)


Gord Dibben MS Excel MVP

 

[Tyro]

Mr. Biddulph:



I have been programming for over 40 years. I work mostly in operating
systems. I can make the most convoluted and obfuscated formulas you can
imagine. I choose not to because it is better to present a simple formula
and let the person receiving the formula to play with it as desired.



Tyro