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]

that will be a lot more complicated I think to do that
as we are just looking at the MAX and MIN
so any that are MIN or MAX are ALL highlighted

I'll have a think

 

[etaf]

If only 2 D10 AND E10=539, then E10 would be red.

BUT you said

AND D10=E10, then E10 must be green AND D10 must be red.

 

[hsandeep]

BUT you said

BUT you said

BUT you said

• There would be maximum 1 highest & maximum 1 lowest value in D10:Y10.
• Generation of values ‘starts’ from D10 & then ‘moves’ cell by cell up to Y10
• 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.
• If only 2 cells are <>0 (this would happen only D10<>0 AND E10<>0) AND rest all F10:Y10=0, then E10 would be green and D10 would be red PROVIDED E10>=D10
• If only 2 choices are left to be made for green or red, then the farthest would be green & therefore the leftover farthest cell would be red. Example: D10:W10=500 AND X10=666 AND Y10=666. So here Y10 would be green & W10 would be red (Since W10 is farthest amongst all lowest cells)

Here 1 more example: if all D10:Y10=666, then Y10 as green & X10 as red
2nd example: D10:H10=666 AND rest all I10:Y10=0, then H10 as green & G10 as red

If only 2 D10 & E10 are <>0 AND rest all F10:Y10=0 AND D10=E10, then & then only E10 would be green & D10 would be red. (Criteria #4 & 5, since only 2 choices)

But if D10=500, E10=500, F10=666 AND rest all G10:Y10=0, then F10 is green (this is obvious). Now we are left with D10 & E10 cells for red to be chosen? Since E10 is farthest from D10, so E10 is red

 

[etaf]

i have had a look, although other events have taken my time away, and not yet found a solution,

 

[hsandeep]

i have had a look, although other events have taken my time away, and not yet found a solution,

Hi etaf,
I will wait for the solution...I know you have understood the various criterias well & a 'rough' solution is already floating in your mind...only you need to polish the formula & make it error-free & to face all the criterias