Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
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 Aug 22 '13 at 9:00
    
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 Aug 22 '13 at 9:08
    
Ah, thank you very much. it works now :) –  Ranayna Aug 22 '13 at 9:17
    
Your welcome :) –  CLockeWork Aug 22 '13 at 9:28

You can use a COUNTIF as alternative:

=COUNTIF(A1,0)

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.