Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding highest values | Excel Worksheet Functions | |||
Finding the highest values | Excel Discussion (Misc queries) | |||
Finding and Formatting the 5 highest values in a range? | Excel Programming | |||
Finding (Multiple) Highest Values in Column | Excel Worksheet Functions | |||
3 highest values indicated using formatting | Excel Worksheet Functions |