Finding and Formatting the 5 highest values in a range?
 

Hey all,


I am trying to figure out a way to format a set of numbers in an XL sheet
through a macro so that the number with the max value has a cell color
of red, the next max value has a cell color of yellow, the 3rd max value
has a cell color of blue, the 4th max value has a cell color of green, and
the 5th max value has a cell color of grey. If this is confusing replace
the "max value" with "top scoring". There will not be any ties.


For example

02
23 (yellow)
05
27 (red)
08
19 (blue)
01
10 (grey)
00
14 (green)
06


Has anyone done something similar to this and/or have suggestions?

Lance

Finding and Formatting the 5 highest values in a range?
 

WE answered this yesterday.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"lance" wrote in message
...
Hey all,


I am trying to figure out a way to format a set of numbers in an XL sheet
through a macro so that the number with the max value has a cell color
of red, the next max value has a cell color of yellow, the 3rd max value
has a cell color of blue, the 4th max value has a cell color of green, and
the 5th max value has a cell color of grey. If this is confusing replace
the "max value" with "top scoring". There will not be any ties.


For example

02
23 (yellow)
05
27 (red)
08
19 (blue)
01
10 (grey)
00
14 (green)
06


Has anyone done something similar to this and/or have suggestions?

Lance

Finding and Formatting the 5 highest values in a range?
 

One approach is to use advanced filter to move data to a new range sorted in
descending order then the cells 1 - 5 of that range are the top 5 in order.
now use match to locate the 5 values on your list and format the resulting
address.

I haven't done it recently but if you macro record you should get 80%

"lance" wrote:

Hey all,


I am trying to figure out a way to format a set of numbers in an XL sheet
through a macro so that the number with the max value has a cell color
of red, the next max value has a cell color of yellow, the 3rd max value
has a cell color of blue, the 4th max value has a cell color of green, and
the 5th max value has a cell color of grey. If this is confusing replace
the "max value" with "top scoring". There will not be any ties.


For example

02
23 (yellow)
05
27 (red)
08
19 (blue)
01
10 (grey)
00
14 (green)
06


Has anyone done something similar to this and/or have suggestions?

Lance