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.

 

[awoohaw]

@Joe4 , i'm a goob.

 

[Joe4]

@Joe4 , i'm a goob.

No worries! It happens to all of us. Sometimes, you get so far down a path that you can lose the forest for the trees!
Sometimes it helps to take a step back adn re-examine the question. Being away overnight gave me a fresh perspective.

 

[awoohaw]

i am so sorry. Here is a corrected version:

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNTIF(C2:C5,0)=(COUNTA(C:C)-1)	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

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNTIF(C2:C5,0)=(COUNTA(C:C)-1)	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

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	3
ItalianPlatinum

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNTIF(C2:C5,0)=(COUNTA(C:C)-1)	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]

If you want it to be red if at least one is not zero, you could use:

=COUNT(C2:C5)-COUNTIF(C2:C5,"0")>0

or more simply:

=COUNTIF(C2:C5,"<>0")>0

ok seems to be working with one odd item. my conditional is green with black font. but its green with white font? I tried expanding with larger dataset to see how it will operate with my live data

 

[Joe4]

ok seems to be working with one odd item. my conditional is green with black font. but its green with white font?

Go back and check the formatting portion of that green CF rule.
Check the Font tab and see what you have the color set to. If you want it to stay blank, it should be "Automatic".

 

[ItalianPlatinum]

i am so sorry. Here is a corrected version:

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNTIF(C2:C5,0)=(COUNTA(C:C)-1)	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

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNTIF(C2:C5,0)=(COUNTA(C:C)-1)	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

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	3
ItalianPlatinum

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=COUNTIF(C2:C5,0)=(COUNTA(C:C)-1)	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

Thanks this one looks to be working I am going to implement to my live test

 

[ItalianPlatinum]

I spoke too soon. few things. the hearder isnt red with 1 record flagging. and how do I solution blank cells that i could have in my data set. Do i just create another conditional format to clear out if any blank cells and place it last?

 

[awoohaw]

try using this formula then:
=COUNTIF(C2:C5000,0)=COUNTA(C2:C5000)

 

[ItalianPlatinum]

try using this formula then:
=COUNTIF(C2:C5000,0)=COUNTA(C2:C5000)

Still not working

 

[ItalianPlatinum]

Go back and check the formatting portion of that green CF rule.
Check the Font tab and see what you have the color set to. If you want it to stay blank, it should be "Automatic".
View attachment 88240

I noticed if I don't do stop if true it'll flag green shade and white font. BUT if i do stop if true for the GREEN it does the correct conditional shading (green shade and automatic font). is that weird to you?