Pearson Software Consulting Services

Sorting By Color

 If you have color-code cells in your worksheet, you find that at times it is useful to sort rows by the colors of the cells.  That is, sort all the reds at the top, followed by the blues, followed by the yellows, and so on.Unfortunately, Excel provides no such tool. You have to do it manually.  This page describes how to do it. The first thing you need to do is create an additional column that will contain the ColorIndex (click here for more information about the ColorIndex) of either the font or the background of the cell.  To the right of the data you want to sort, insert a new column by selecting the cell the right of the data, and choosing Columns from the Insert menu. Next, you need a VBA function to return the ColorIndex value of the cell.  Put the following code in a standard code module in your workbook. Function ColorIndexOfCell(Rng As Range, _     Optional OfText As Boolean, _     Optional DefaultAsIndex As Boolean = True) As Integer Dim C As Long If OfText = True Then     C = Rng.Font.ColorIndex Else     C = Rng.Interior.ColorIndex End If If (C < 0) And (DefaultAsIndex = True) Then     If OfText = True Then         C = GetBlack(Rng.Worksheet.Parent)     Else         C = GetWhite(Rng.Worksheet.Parent)     End If End If ColorIndexOfCell = C End Function Function GetWhite(WB As Workbook) As Long     Dim Ndx As Long     For Ndx = 1 To 56         If WB.Colors(Ndx) = &HFFFFFF Then             GetWhite = Ndx             Exit Function         End If     Next Ndx     GetWhite = 0 End Function Function GetBlack(WB As Workbook) As Long     Dim Ndx As Long     For Ndx = 1 To 56         If WB.Colors(Ndx) = 0& Then             GetBlack = Ndx             Exit Function         End If     Next Ndx     GetBlack = 0 End Function   Then, in the newly created column, enter either of the following formulas: If you want to sort by the Background color of the cell, use the formula =ColorIndexOfCell(A1,FALSE,TRUE) If you want to sort by the Font color of the cell, use the formula =ColorIndexOfCell(A1,TRUE,TRUE) Of course, change the reference A1 to the first cell in the range.  Use Edit, Fill, Down to fill this formula down to the entire range of data you want to sort. In these cells, you'll see numbers between 1 and 56.  Each of the values indicates the ColorIndex of the cell. Now, you can sort your data in the normal way, but choose the new column as the primary or first sort key. The cells will be sorted in ascending (or descending) order of the ColorIndex values. So far, this is all well and good if you are happy with the default order of ColorIndex values.  For example, by default, Red = 2, Blue= 5, and Yellow = 6.  Therefore, when sorting by ColorIndex values, the data will list all the reds first, followed by the blues, then the yellows. If you want to modify this order, you will need to create a "custom list" and tell Excel to use this list as the sort order.  First, create a custom list by going to the Tools menu, Options item, Custom Lists tab, and selecting NEW LIST in the Custom Lists box.  Then, enter the ColorIndex values in the order you want them to appear in ascending sorts, in the List Entries box. You can enter the numeric values (between 1 and 56) on separate lines in the List Entries box (create a new line by pressing ALT+ENTER) or by separating the entries with a comma all on the same line. (NOTE: Non-USA English users may have to use a semicolon rather than a comma.)  For example, to sort in order Blue, Yellow, Red, the custom list would be (without the quotes) "5,6,2". Then, in the Sort dialog box, click the Sort By drop down box, click the Options button, and choose this new list from the lists displayed. Yes, sorting by color is a bit tricky, and something that we all would like to see built in to Excel. However, until Microsoft provides this feature as a built in tool, we must make the best of what is available. NOTE: This method sorts by the color specified by the cell's properties.  It does NOT work with colors that are displayed as a result of Conditional Formatting. See also Colors In Excel and Conditional Formatting Colors.

Created By Chip Pearson and Pearson Software Consulting, LLC