Color every Cell that contains the number '0'

My problem is most likely a completely trivial one, but at the moment I'm completely out of ideas.

I want to color all cells in a certain working area depending on what single digit number they contain. The conditional formatting rules I created for the numbers 1 to 9 work like a charm, but I absolutely can't get it to work for the 0.

If I create a simple Rule, "Format only cells that contain", Cell Value equal to 0, all cells that contain a 0 are colored, but so are also all cells that are empty.

If I try to use a formula to color the cells, it is not working at all. I tried the following formulas:

=AND($A$1:$O$35<>"",$A$1:$O$35="0")

=AND(ISNUMBER($A$1:$O$35),$A$1:$O$35="0")

and several variations of those. When I use similar formulas in normal cells, they work as expected, regarding to the TRUE or FALSE response. When I use them in the conditional formatting, no cell is colored.

Ranayna

Posted 2013-08-22T08:24:26.840

Reputation: 193

Answers

I was thinking there must be a simple answer to this, then I tried it myself and yep, =A1=0 returns as 0 when blank!

I'm not sure why, but I found that swapping the statements in your first AND statement worked

=AND($B$2=0,$B$2<>"")

UPDATE
Following our comments: You're referencing an entire range in your formula, but conditional formatting checks each cell within your selected range against your formula. Instead select the entire range you want to run the formatting against and then for the formula set the top left cell as the basis for your formula with no reference locking: =AND(A1=0,A1<>"")

This will run through each cell in the range, checking to see if that cell fits the conditions set.

CLockeWork

Posted 2013-08-22T08:24:26.840

Reputation: 1 887

That works, when i apply it to a single cell, like you did. Most likely i am missing some basic excel concept when creating a rule that should affect more cells. – Ranayna – 2013-08-22T09:00:45.993

Ah, looking again at your formula, it makes sense now. Conditional formatting checks each cell in the selected range against the same formula. You have selected a range as your formula, so for each cell in the selection it checks to see if that range represents 0. Instead reference the first cell in the selection and remove row and column fixings, then the rule will go through each cell, checking if that cell fits. Select you range then put this as the formula: =AND(A1=0,A1<>"") – CLockeWork – 2013-08-22T09:08:55.910

You can use a COUNTIF as alternative:

=COUNTIF(A1,0)

as the formula for conditional formatting. Blanks won't be highlighted.

Jerry

Posted 2013-08-22T08:24:26.840

Reputation: 4 716