Sorting By Color

This page describes how to sort a range by color.

Introdution

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:

=ColorIndexOfOneCell(B1,FALSE,1)

Enter this cellin the new column on the row on which the data to be sorted occurs. 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.