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]

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

right kind of what I was trying to show you that it isnt working for me.....

 

[Joe4]

Try placing your formula in some blank cells just to see what they return, i.e.

=COUNTIF(C2:C5,0)

What value does it return?

Also, in your region, what does Excel use as argument separators? Commas or semi-colons?
If semi-colons, then your formula will need to look like:

=COUNTIF(C2:C5;0)

 

[awoohaw]

right kind of what I was trying to show you that it isnt working for me.....

Regarding your Post #7. You only have two of the conditional formulas displayed.
And all you have is the fill colors reversed, if you reverse the fill colors for the conditional formatting in Cell C1, then you should get a correct CF.

 

[awoohaw]

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

 

[ItalianPlatinum]

Regarding your Post #7. You only have two of the conditional formulas displayed.
And all you have is the fill colors reversed, if you reverse the fill colors for the conditional formatting in Cell C1, then you should get a correct CF.

i copied your formulas exactly. do you see one that is different than how you have it? and yes i didnt do c2:c5 yet as I am trying to get C1 to work first

 

[ItalianPlatinum]

Try placing your formula in some blank cells just to see what they return, i.e.

=COUNTIF(C2:C5,0)

What value does it return?

Also, in your region, what does Excel use as argument separators? Commas or semi-colons?
If semi-colons, then your formula will need to look like:

=COUNTIF(C2:C5;0)

Shows as zero and i never have used semi-colons so comma should be it.

 

[awoohaw]

Icannot see the entire formula. Are you sure you copied/typed it completely?

=COUNTIF(C2:C5,0)=0

 

[Joe4]

View attachment 88222

Shows as zero and i never have used semi-colons so comma should be it.

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?

 

[awoohaw]

Icannot see the entire formula. Are you sure you copied/typed it completely?

=COUNTIF(C2:C5,0)=0

The objective of typing the CF formula in a "practice" cell is to get to where your return values of TRUE or FALSE, although any non zero number is TRUE, and 0 is FALSE, I like to be sure see TRUE or FALSE, it doesn't leave room for ambiguity.

 

[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

little confused your first picture shows conditional formatting green

Icannot see the entire formula. Are you sure you copied/typed it completely?

=COUNTIF(C2:C5,0)=0