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]

Ar eyou still using 2010 version

how about

=D10=MINIFS($D$10:$Y$10,$D$10:$Y$10,"<>0")
and
=D10=MAXIFS($D$10:$Y$10,$D$10:$Y$10,"<>0")

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
10	1	0	4	5	0	0	0	0	0	0	0	0	0	12	3
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:S10	Expression	=D10=MAXIFS($D$10:$Y$10,$D$10:$Y$10,"<>0")	text	NO
D10:S10	Expression	=D10=MINIFS($D$10:$Y$10,$D$10:$Y$10,"<>0")	text	NO

otherwise i will post a 2010 version

 

[hsandeep]

Ar eyou still using 2010 version

how about

=D10=MINIFS($D$10:$Y$10,$D$10:$Y$10,"<>0")
and
=D10=MAXIFS($D$10:$Y$10,$D$10:$Y$10,"<>0")

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
10	1	0	4	5	0	0	0	0	0	0	0	0	0	12	3
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:S10	Expression	=D10=MAXIFS($D$10:$Y$10,$D$10:$Y$10,"<>0")	text	NO
D10:S10	Expression	=D10=MINIFS($D$10:$Y$10,$D$10:$Y$10,"<>0")	text	NO

otherwise i will post a 2010 version

Hi etaf
Did not worked. I made D10=537, E10=538; F10:Y10=0...nothing happened. Probably, i need a 2010version.

 

[etaf]

Probably, i need a 2010version.

if you have 2010 version
then
=D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10))
=D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10))

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
10	1	0	4	5	0	0	0	0	0	0	0	0	0	12	3
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:S10	Expression	=D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10))	text	NO
D10:S10	Expression	=D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10))	text	NO

 

[hsandeep]

if you have 2010 version
then
=D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10))
=D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10))

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
10	1	0	4	5	0	0	0	0	0	0	0	0	0	12	3
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:S10	Expression	=D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10))	text	NO
D10:S10	Expression	=D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10))	text	NO

This works. But when only D10 has value AND rest all E10:Y10=0, then I don't want green or red. Slight modification in the code requested.

 

[etaf]

what happens if D11 only has a value - is that highlighted ?
OR is it only if D10 has a value and E10-Y10 is zero - 0

All cells are 0 or a number - no blanks - as stated in above

What colour if you have say
20 in 2 cells only F10 & J10
thats both MAX and MIN - what colour is prioritised - RED or GREEN cannot be both , so the order is important

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
10	1	0	1	0	3	0	3	0	0	0	0	0	0	0	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:S10	Expression	=AND(D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO
D10:S10	Expression	=AND(D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

 

[hsandeep]

what happens if D11 only has a value - is that highlighted ?
OR is it only if D10 has a value and E10-Y10 is zero - 0

All cells are 0 or a number - no blanks - as stated in above

What colour if you have say
20 in 2 cells only F10 & J10
thats both MAX and MIN - what colour is prioritised - RED or GREEN cannot be both , so the order is important

Book3
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
10	1	0	1	0	3	0	3	0	0	0	0	0	0	0	0
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D10:S10	Expression	=AND(D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO
D10:S10	Expression	=AND(D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

what happens if D11 only has a value - is that highlighted ? D11 never has any value
only if D10 has a value and E10-Y10 is zero - 0 No color in D10 & No color in E10:Y10
What colour if you have say
20 in 2 cells only F10 & J10 It will never happen that D10=0, E10=0 AND F10=20 (or any number), J10=20 (or any number). Number generations starts from D10 & unless it fills D10 with <>0, it does not starts filling E10 with any number. Till then E10=0. Similarly now F10 will remain equal to 0 unless E10<>0, Similarly now G10 will remain equal to 0 unless F10<>0 & so on till Y10<>0.
It will never be D10:I10=0, J10=2000, K10=0, L10=2000
If there is a number in L10, then D10:K10<>0...
here it may happen that cells after L10 i.e. M10:Y10=0.
It may happen that D10=20, E10=10, F10=20 AND rest all G10:Y10=0, then the latest highest cell (here F10) will be green & E10 will be red.
Suppose, D10=100, E10=100 then E10 will be green & D10 will be red.

 

[etaf]

how about
=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))
=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))

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	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(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(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=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

 

[hsandeep]

how about
=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(IF($D$10:$Y$10<>0,$D$10:$Y$10)))
=AND(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))

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	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(COUNTIF($E$10:$Y$10,0)<>21,D10<>0,D10=MAX(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=MIN(IF($D$10:$Y$10<>0,$D$10:$Y$10)))	text	NO

Awesome, breath taking...code works etaf. Only 1 thing when D10:E10<>0 AND rest all F10:Y10=0 AND D10=E10, then E10 must be green AND D10 must be red. Example: D10=539, E10=539, F10:Y10=0, it is showing both D10 & E10 as green!!! Here it should be D10 in red & E10 in green

 

[etaf]

i have setup 2 additional rules now


RED
=AND($D$10=$E$10,COUNTIFS(F$10:$Y$10,0)=20)
GREEN
=AND($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=$E$10,COUNTIFS(F$10:$Y$10,0)=19)	text	YES
D10:Y10	Expression	=AND($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	1	0	5	0	0	8	0	1	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=$E$10,COUNTIFS(F$10:$Y$10,0)=19)	text	YES
D10:Y10	Expression	=AND($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