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.

 

[ItalianPlatinum]

And it looks like it is showing cell C1 as green, which is what it should be doing.
This is what you wanted, right?
So what exactly is still the issue?

C1 is green if i don't have any breaks (C2:C5). C1 in this scenario should be red seeing at least 1 item isn't zero.

 

[ItalianPlatinum]

I hope this explains it better:

mr excel questions 17.xlsm
	A	B	C	D	E	F	G
1	Pre	Post	Differences		CF Formula to paste into CF	Into CF dialog for these cells
2	1	2	-1	FALSE	=COUNTIF(C2:C5,0)=0	C1	Makes C1 GREEN when all are zero
3	1	1	0	TRUE	=COUNTIF(C2:C5,0)>0	C1	Makes C1 RED when at least one is not zero
4	2	2	0	TRUE	=(C2<>0)	C2, then copy format	Makes C2 GREEN when C2 zero
5	3	4	-1	FALSE	=(C2=0)	C2, then copy format	Makes C2 RED when C2 is not zero
ItalianPlatinum

Cell Formulas
Range	Formula
D2	D2	=COUNTIF(C2:C5,0)=0
E2:E5	E2	=FORMULATEXT(D2)
D3	D3	=COUNTIF(C2:C5,0)>0
D4	D4	=(C2<>0)
D5	D5	=(C2=0)

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

is it just me or is the first picture saying one thing and the 3 picture is saying something else?

 

[awoohaw]

is it just me or is the first picture saying one thing and the 3 picture is saying something else?

View attachment 88227

They are the same to me. ignore the colors in the top image. All I did was paste the rule in column E. They have nothing to do with color of the adjacent cells.


Just focus on the instructions:

mr excel questions 17.xlsm
	E	F	G
1	CF Formula to paste into CF	Into CF dialog for these cells
2	=COUNTIF(C2:C5,0)=0	C1	Makes C1 GREEN when all are zero
3	=COUNTIF(C2:C5,0)>0	C1	Makes C1 RED when at least one is not zero
4	=(C2<>0)	C2, then copy format	Makes C2 GREEN when C2 zero
5	=(C2=0)	C2, then copy format	Makes C2 RED when C2 is not zero
ItalianPlatinum

Cell Formulas
Range	Formula
E2:E5	E2	=FORMULATEXT(D2)

 

[ItalianPlatinum]

The header should flag green if ALL records are zero (below C1) or flag red if ANY record is not zero (below C1). Hope this clarifies the confusion. to note my dataset is huge 1,000 records. Trying to show the user go find the variance if something isn't zero as zero is ideal. And if they are all zero show green so the user can just stop their review

 

[awoohaw]

The header should flag green if ALL records are zero (below C1) or flag red if ANY record is not zero (below C1). Hope this clarifies the confusion. to note my dataset is huge 1,000 records. Trying to show the user go find the variance if something isn't zero as zero is ideal. And if they are all zero show green so the user can just stop their review

and that is what the conditional formatting formula in ROW 2 of POST #23 does, provided you have selected GREEN as the FILL Color.

 

[ItalianPlatinum]

It must be me and I didn't have my coffee or something but I have exactly what you have? Do you see something different that i don't see. first picture is what I have 2nd picture is what you have right? you have red greater than 0 and green equal to zero. when i pump that through my test its flagging green.

 

[Joe4]

and that is what the conditional formatting formula in ROW 2 of POST #23 does, provided you have selected GREEN as the FILL Color.

I don't think that is correct. I think you have it backwards.

The formula:

=COUNTIF(C2:C5,0)=0

does NOT check that ALL the entries are zero, if checks that NONE of the entries are zero.

 

[Joe4]

One way to check to see if all the entries are zero is like this:

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

 

[ItalianPlatinum]

One way to check to see if all the entries are zero is like this:

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

That worked for all zeroes! BINGO. Now the red isn't quite working

example if all my records aren't zero it should be red as well. the red only seems to work if at least 1 zero exists

 

[Joe4]

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