Highlighting highest & lowest numbers using CF

[hsandeep]

I have a contiguous row D10:Y10 which generates +ve numerical values OR 0 (but not null or spaces/(s)).

Using CF, I want to highlight background cell with ‘green’ color, for highest value from D10:Y10 & with ‘red’ color for lowest value.

Please note:

• By default, 0 is generated in all D10:Y10
• If all cells in D10:Y10=0, then I don’t want ‘green’ or ‘red’ color.
• Generation of values ‘starts’ from D10 & then ‘moves’ cell by cell up to Y10 example D10=43.52, E10=63.79, F10=88.12, G10:Y10=0

How to accomplish? Thanks in advance.

 

[etaf]

IGNORE LAST POST
that did not work with D10 & E10 = 0

change here

RED
=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20)
GREEN
=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19)

and put them at first and 2nd in the list/order - you have an arrow - top right to change the order
and also ticked STOP IF TRUE

BUT this is now very specific for the range and rules

I may have a think about how to incorporate into the existing rules , as that may simply be adding an OR () - but have a busy day today with grandchildren , so may not get a chance

meanwhile here is the quick solution

Not tested for all possibilities , so you may need to test it throughly

when D&E same

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	4	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19)	text	YES
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20)	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES

other

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	4	4	0	0	0	0	0	0	0	0	12	0	1	0	0	0	0	0	0	0	0	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19)	text	YES
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20)	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES

all zero

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19)	text	YES
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20)	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES

 

[hsandeep]

IGNORE LAST POST
that did not work with D10 & E10 = 0

change here

RED
=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20)
GREEN
=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19)

and put them at first and 2nd in the list/order - you have an arrow - top right to change the order
and also ticked STOP IF TRUE

BUT this is now very specific for the range and rules

I may have a think about how to incorporate into the existing rules , as that may simply be adding an OR () - but have a busy day today with grandchildren , so may not get a chance

meanwhile here is the quick solution

Not tested for all possibilities , so you may need to test it throughly

when D&E same

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	4	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19)	text	YES
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20)	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES

other

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	4	4	0	0	0	0	0	0	0	0	12	0	1	0	0	0	0	0	0	0	0	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19)	text	YES
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20)	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES

all zero

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19)	text	YES
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20)	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	YES

etaf Enjoy your weekend...still testing your formula for all combinations....but I think the formula can be combined in 1 formula...this formula is very 'sensitive' & would require frequent daily monitoring

 

[etaf]

this formula is very 'sensitive' & would require frequent daily monitoring

not sure what you mean ,

but I think the formula can be combined in 1 formula

probably

 

[hsandeep]

not sure what you mean ,


probably

I tried with 'other' (out of 3 combinations given...though all 3 combinations seems to be SAME PROBABLY) by pasting the 4 formulas in CF. But D10=539, E10=539, F10=540 AND rest all G10:Y10=0, it gave E10 as green & D10 as green (wrong) Should have given F10 as green & E10 as red.

 

[etaf]

3 examples are all the same formula - just showing different results.

 

[hsandeep]

3 examples are all the same formula - just showing different results.

Okay. But when applied in the CF, it gives wrong colors; D10=539, E10=539, F10=540 AND rest all G10:Y10=0, it gave E10 as green & D10 as green (wrong) Should have given F10 as green & E10 as red.(correct)

 

[etaf]

is that the only error now - need to rework out the code

 

[hsandeep]

is that the only error now - need to rework out the code

Yes

 

[etaf]

this may throw up other errors
and not really great code now

any way - 3 different values shown in the range , all same formulas - to show the fix - hopefully

Book3.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	539	539	666	1	0	12	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20,$F$10=0)	text	NO
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19,$F$10=0)	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

Book3.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	539	539	666	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20,$F$10=0)	text	NO
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19,$F$10=0)	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

Book3.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	539	539	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20,$F$10=0)	text	NO
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19,$F$10=0)	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

 

[hsandeep]

this may throw up other errors
and not really great code now

any way - 3 different values shown in the range , all same formulas - to show the fix - hopefully

Book3.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	539	539	666	1	0	12	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20,$F$10=0)	text	NO
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19,$F$10=0)	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

Book3.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	539	539	666	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20,$F$10=0)	text	NO
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19,$F$10=0)	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

Book3.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
10	539	539	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:Y10	Expression	=AND($D$10<>0,$D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20,$F$10=0)	text	NO
D10:Y10	Expression	=AND($D$10<>0, $D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=19,$F$10=0)	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO
D10:Y10	Expression	=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

Seems like very near the target. Formulas I think ...still trying...is able to adjust itselfs to meet all the scenarios EXCEPT the 1 example taken by you itself. D10=539, E10=539, F10=666 AND rest all G10:Y10 =0; F10 as green (correct) but there would be only 1 lowest (<>0). So D10 AND E10 both can't be red. In such cases cell address whose column is farthest from D10 would be red. If only 2 D10 AND E10=539, then E10 would be red.
Had it been
D10=539, E10=539, F10=539, G10=666, H10=666, I10=666 AND rest all J10:Y10=0, then I10 must be green & F10 must be red.