Conditional Format based on a range

[TTP]

Please help to conditional format cell A1 --> all cells in a column D (range) need to have the same answer e.g. either ALL need to be YES to be green in A1 or NO to be red.

Formula needs to be based on a range of cells

 

[Joe4]

You should simply be able to use a COUNTIF function in your Conditional Formatting formula, i.e.

=OR(COUNTIF(D1:D7,"Yes")=7,COUNTIF(D1:D7,"No")=7)

 

[awoohaw]

try this:

Book1
	A	B	C	D
1				yes
2				yes
3				yes
4				YES
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=COUNTIF($D$1:$D$4,"NO")=COUNTA($D$1:$D$4)	text	NO
A1	Expression	=COUNTIF($D$1:$D$4,"YES")=COUNTA($D$1:$D$4)	text	NO

 

[Joe4]

try this:

Book1
	A	B	C	D
1				yes
2				yes
3				yes
4				YES
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=COUNTIF($D$1:$D$4,"NO")=COUNTA($D$1:$D$4)	text	NO
A1	Expression	=COUNTIF($D$1:$D$4,"YES")=COUNTA($D$1:$D$4)	text	NO

Note that using COUNTA could be problematic if it is possible to have empty cells in your range, and you only want the condition met if every cell is populated with a Yes/No value.

 

[awoohaw]

Note that using COUNTA could be problematic if it is possible to have empty cells in your range, and you only want the condition met if every cell is populated with a Yes/No value.

View attachment 104802

thanks Joe,

Here is corrected CF:

Book1
	A	B	C	D
1				yes
2				yes
3
4				yes
5				YES
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=COUNTIF($D$1:$D$5,"NO")=ROWS($D$1:$D$5)	text	NO
A1	Expression	=COUNTIF($D$1:$D$5,"YES")=ROWS($D$1:$D$5)	text	NO

Book1
	A	B	C	D
1				yes
2				yes
3				yes
4				yes
5				YES
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=COUNTIF($D$1:$D$5,"NO")=ROWS($D$1:$D$5)	text	NO
A1	Expression	=COUNTIF($D$1:$D$5,"YES")=ROWS($D$1:$D$5)	text	NO

Book1
	A	B	C	D
1				no
2				NO
3				NO
4				NO
5				NO
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=COUNTIF($D$1:$D$5,"NO")=ROWS($D$1:$D$5)	text	NO
A1	Expression	=COUNTIF($D$1:$D$5,"YES")=ROWS($D$1:$D$5)	text	NO

 

[Joe4]

Looks good.
Uses same concept as mine, just makes the formulas a little more dynamic, in case you don't want to hard-code the number of cells in the formula!

 

[awoohaw]

Joe,
In your solution, where is the conditional formatting rule? If it is based on this formula being true:

=OR(COUNTIF(D1:D7,"Yes")=7,COUNTIF(D1:D7,"No")=7)

How do you know with this rule to make the cell red or green?

 

[Joe4]

Joe,
In your solution, where is the conditional formatting rule? If it is based on this formula being true:

=OR(COUNTIF(D1:D7,"Yes")=7,COUNTIF(D1:D7,"No")=7)

How do you know with this rule to make the cell red or green?

Ah, I misread and missed that they wanted two different colors, and just thought they wanted one color (same color) if ALL were "Yes" or ALL were "No".
If they want different colors for "Yes" and "No" (which I now see is probably what they want), they would need to break it up into two rules, like you did:

Green rule:
=COUNTIF(D1:D7,"Yes")=7

Red rule:
=COUNTIF(D1:D7,"No")=7

 

[TTP]

You should simply be able to use a COUNTIF function in your Conditional Formatting formula, i.e.

=OR(COUNTIF(D1:D7,"Yes")=7,COUNTIF(D1:D7,"No")=7)

Thank you - but when all are no the cell also now goes green rather than red - does that make sense

thanks Joe,

Here is corrected CF:

Book1
	A	B	C	D
1				yes
2				yes
3
4				yes
5				YES
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=COUNTIF($D$1:$D$5,"NO")=ROWS($D$1:$D$5)	text	NO
A1	Expression	=COUNTIF($D$1:$D$5,"YES")=ROWS($D$1:$D$5)	text	NO

Book1
	A	B	C	D
1				yes
2				yes
3				yes
4				yes
5				YES
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=COUNTIF($D$1:$D$5,"NO")=ROWS($D$1:$D$5)	text	NO
A1	Expression	=COUNTIF($D$1:$D$5,"YES")=ROWS($D$1:$D$5)	text	NO

Book1
	A	B	C	D
1				no
2				NO
3				NO
4				NO
5				NO
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	Expression	=COUNTIF($D$1:$D$5,"NO")=ROWS($D$1:$D$5)	text	NO
A1	Expression	=COUNTIF($D$1:$D$5,"YES")=ROWS($D$1:$D$5)	text	NO

Thank you