Conditional Formatting Help

[ItalianPlatinum]

Hello I have what I thought was an easy modification, but having issues finding an easy way to solution. I have 3 columns one column is pre, one is post, and the 3rd is differences. I have existing conditional formatting in column C to highlight red if the data isn't zero and green if the data is zero. The new condition i was looking to add is if any data below the header equals zero to highlight the header green and if any don't equal zero to highlight the header red.

Pre	Post	Differences
1	2	-1
1	1	0
2	2	0
3	4	-1

So in this scenario "Differences" would be shaded red as well as the first and last record. If those records were zero then all 5 in column C would be shaded green including the header "Differences." Any help is much appreciated as always.

 

[Joe4]

Try using the COUNTIF formula in your Conditional Formatting to count the number of zeroes.

 

[awoohaw]

Try this:

mr excel questions 17.xlsm
	A	B	C
1	Pre	Post	Differences
2	1	2	-1
3	1	1	0
4	2	2	0
5	3	4	-1
6
ItalianPlatinum

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNTIF(C2:C5,0)=0	text	NO
C2:C5	Expression	=(C2=0)	text	NO
C2:C5	Expression	=(C2<>0)	text	NO
C1	Expression	="COUNTIF(C2:C5,0)=0"	text	NO
C1	Expression	=COUNTIF(C2:C5,0)>0	text	NO

 

[awoohaw]

Try this:

mr excel questions 17.xlsm
	A	B	C
1	Pre	Post	Differences
2	1	2	-1
3	1	1	0
4	2	2	0
5	3	4	-1
6
ItalianPlatinum

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNTIF(C2:C5,0)=0	text	NO
C2:C5	Expression	=(C2=0)	text	NO
C2:C5	Expression	=(C2<>0)	text	NO
C1	Expression	="COUNTIF(C2:C5,0)=0"	text	NO
C1	Expression	=COUNTIF(C2:C5,0)>0	text	NO

i don't know why there is a 5th format.. let me find out which is not good.

 

[awoohaw]

here is a better version:

mr excel questions 17.xlsm
	A	B	C	D
1	Pre	Post	Differences
2	1	2	-1
3	1	1	0
4	2	2	0
5	3	4	-1
6
ItalianPlatinum

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNTIF(C2:C5,0)=0	text	NO
C2:C5	Expression	=(C2=0)	text	NO
C2:C5	Expression	=(C2<>0)	text	NO
C1	Expression	=COUNTIF(C2:C5,0)>0	text	NO

 

[ItalianPlatinum]

For some reason I am not getting the same results as you I will troubleshoot a bit to make sure not a me issue

 

[ItalianPlatinum]

see anything i dont see?

 

[awoohaw]

when you created those conditional formats was Cell C1 selected? I know it says "applies to" may mean you were in that cell.
When I create CF formulas, I create the formula in a worksheet cell. So for example i would type in Cell D1, =COUNTIF(C2:C5,0)=0... and when that says TRUE then I know I have my GREEN color correct. Copy the formula from withing the cell. Select cell C1, then add the conditional formatting rule. Just be sure the absolute/relative cell references are correct.

But, to answer your question, I do not see what could be wrong.

 

[ItalianPlatinum]

Does this answer your concern?

 

[awoohaw]

It looks like you got it. Good job! But I thought you wanted Green when there were all zeros?