Color, Sorting on Color

Home page:
[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

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

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

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.

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.


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.


