Color, Sorting on Color

Location: http://www.mvps.org/dmcritchie/excel/colorsort.htm Home page: http://www.mvps.org/dmcritchie/excel/excel.htm [View without Frames]

What is meant by color? There is Color Index that is used by Excel, there is RGB. How are the cells colored, there is normal cell formatting and there is Conditional Formatting. Once you have a number it's not likely you could use it directly for the ordering you want. Finally obtaining normal cell formatting that is current is a problem since a change of color does not cause recalculation of formulas (user defined functions) dependent on coloring.

In any case you will need another column.

Changing the color of a cell will not cause an event to occur that you can pick up automatically, so you would have to make your User Defined Function volatile to make functions work better without intervention. I have not included volatile because it can really slow down use of your workbook. So if there is a basis for coloring cells then you would be better off checking that instead of the actual color (applies to C.F. as well).

You can use Ctrl+Alt+F9 which Recalculates all cells on all worksheets in all open workbooks instead of using Volatile. Workbooks are recalculated when a workbook is opened so you would never be completely behind.

Determining Interior Color of Another Cell
Color Palette -- the 56 Excel Colors
http://www.mvps.org/dmcritchie/excel/colors.htm

The functions below will supply a value when the formula is entered or when sheet is recalculated, as a change in format does not force recalculation.

   Function showColorIndex(rcell)
      showColorIndex = rcell.Interior.ColorIndex
   End Function

  =ShowColorIndex(B2)

Determining Interior Color of Another Cell

   Function showRGB(rcell)
       'Show RGB hex color value of another cell
       showRGB = Right("000000" & Hex(rcell.Interior.Color), 6)
   End Function

=ShowRGB(A1)

An example of using a Case Statement to reassign a color index number to something more suitable for the order you want can be found in the Google Archives. http://groups.google.com/groups?as_umsgid=u0KJ2S9zAHA.1456%40tkmsftngp02

Finally you should be aware that people can change any of the 56 colors in the color index -- not recommended, if you want consistent results.

Conditional Formatting is done by formulas so you would use those formulas on your worksheet.

Color Palette -- the 56 Excel Colors, main coverage of Excel colors.
Worksheet Change Events, also has an example of a Case Statement.
SortByColor «, Chip Pearson, (also see my Colors page and usage with SUBTOTAL). additonal notes w

You are one of many distinguished visitors who have visited my site here or in a previous location since this page was created on December 16, 2000.

Visit [my Excel home page] [Index page] [Excel Onsite Search] [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Color, Sorting on Color

Related