This page has been replaced. Please go to the new Sort By Color page.

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 
This Page:                Updated: November 06, 2013     
MAIN PAGE    About This Site    Consulting    Downloads  
Page Index     Search    Topic Index    What's New   
Links   Legalese And Disclaimers
chip@cpearson.com

Copyright 1997-2007  Charles H. Pearson