Sorting By Color
This page describes how to sort a range by color.
Excel provides no support for sorting a range based on the cells' colors. If you want to sort
based on cell color (by either the background fill color or the font color), you'll need to
use a VBA function to get the numeric ColorIndex value, put that in
a cell, and then sort by those cells. First, you'll need to download the
color functions module, which contains many functions
related to colors. These functions are described on the Color Functions In Excel
page. The function ColorIndexOfOneCell in that module returns the color index value of either the
background color or the font color of a cell.
Once you have downloaded the modColorFunctions zip file, unzip it using your
favorite zip program, such as SecureZip or WinZip. Once you have unzipped the file, open your workbook in
Excel and then press ALT F11 to open the VBA editor (VBE). In the VBE, press
CTRL R to view the Project window if it is not already visible (typically on the
left side of the screen) and select your workbook project in that list. Go to the File menu, choose Import File... item and navigate to and
open the modColorFunctions.bas file that you just unzipped. Once you have
imported the module, its functions are available for use.
In a new or blank column next to your data, enter the formula:
Enter this cellin the new column on the row on which the data to be sorted
Change the reference to B1 to the first cell with a color to be sorted upon. The
FALSE parameter indicates that the function should return the background fill color
of the cell. If you want to use font color, change this parameter to TRUE. The
final parameter, 1 indicates the color index to use if no color has been assigned to
the cell. Now, fill this formula down for as many rows as you have data.
Now, sort your entire range, including the column containing the ColorIndexOfOneCell
functions. The range will sort by the colors. At this point, you have no control over the order of the colors; the
colored cells will sort according to the color index value. To change the sort order of the color index values
to a custom order,
go to the Tools menu in Excel and choose Options. In that dialog, click the Custom Lists tab.
Click Add to create a new list, and enter the color index values in the list box, one item per line, in the
order you wish to sort. Click OK to return to Excel. Then, select the range to sort, go to the Data
menu, choose Sort, and click the Options button. In that dialog, select the custom list you just
created, and then click OK. This will sort the range according to color.
|This page last updated: 25-December-2008.