Conditional Formatting-blank cells vs zeros

[froggiebecki]

Trying to cf a range of cells that may have numbers from 0 to 10, but some will be blank.
0=green,
>0=red
blank should have no formatting

Red's no problem, but the 0 vs blank cells is whipping me.


With just $D$5:$BC$5=0 the 0 cells turn green, but so do the blank cells.

Using this formula to try to leave blank cells unformatted, but fill any that have a "o" in them.

=AND($D$5:$BC$5<>"",$D$5:$BC$5=0)

With the AND statement, nothing turns green.

What am I missing?


Thanks for looking,
Froggiebecki

 

[Gerald Higgins]

Hi, welcome to the board.

I can't replicate this, at least on a simpler version.

=A1=0
This turns green if the cell is blank or 0.

=AND(A1< > "",A1=0)
This turns green with an entry of 0, not green for blank or any other non-0 entry.

Select whichever option you want.

 

[froggiebecki]

Thanks. Figured out I was making it too complicated.

I've got it working, using your suggestion, ended up with 3 simple formulas.

=D$5="" [leaves blank cell blank, and has to be the first rule]
>5 [turns cell red]
=0 [turns it green]

Thanks