Excel - Conditional Formatting - Blank or Zero

Asked By Anni on 11-Jan-08 03:14 PM
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/Pages/T1057_Conditional_Formats_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 replied on 11-Jan-08 03:31 PM
Enter your formating with a formula such as:

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

Tryo
Anni replied on 11-Jan-08 03:34 PM
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 replied on 11-Jan-08 03:48 PM
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 replied on 11-Jan-08 04:13 PM
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
David Biddulph replied on 11-Jan-08 04:14 PM
I do not know why you say you cannot select the whole range and apply the CF
to the whole lot at once.
--
David Biddulph
Tyro replied on 11-Jan-08 04:27 PM
I was simply showing the OP the concept in the formula. You may reduce it to
your liking. Have fun.

Tyro
Tyro replied on 11-Jan-08 04:29 PM
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
Anni replied on 11-Jan-08 04:31 PM
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 replied on 11-Jan-08 04:35 PM
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


On Fri, 11 Jan 2008 12:34:01 -0800, Annie <Annie@>
Tyro replied on 11-Jan-08 04:52 PM
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