How To Sort by Color in Excel?

Excel VBA & Menu Option – Sort By Color

Excel has option to Sort a column based on these 4 items.

1. Sort based on Value
2. Sort based on Cell color or Background color
3. Sort based on Font color
4. Sort based on Cell icon.

In this page, You will learn how to do Sort by cell color. Font color is also pretty much similar and easy to implement.

Lets see the steps do this using Excel menu.

Excel Menu Option to Sort cell By color (Cell Color)

Select the range of cells that needs to be sorted based on cell color. Then follow these steps.

1. Choose ‘Sort & Filter’ in Home menu -> Editing Tab
2. Select ‘Custom Sort’
3. Click ‘Cell Color’ in ‘Sort on’
4. Choose the color in Order, that has to be sorted first.
5. Click add level & then choose the next color to be considered next.
6. and so on till you add all the colors in the column.

Then click Ok. You can now see that the column is sorted based on the Color you have choose in the above steps. Also the order of sorting will follow the order in which the colors are added in Custom Sort.

Sort by Color using Excel VBA

The manual step is easy to do, if the column has very few number of colors. In case if there are so many color variants, then use this ready to use code.

In this code, you don’t have to mention the order of the color. It will be picked automatically as per its first occurrence. (It will take color codes from rows 1 to 100 only. Modify the number of rows in your code accordingly)

'Add Microsoft Scripting Runtime from Tools->Reference
'Dictionary object to Build color Array
Public gDictColHdr As Scripting.Dictionary

'Function to Sort on Color
Sub SortByCellColor()
    Dim dSh As Worksheet, dRow As Double, shName As String
    Dim tSect As String, tHdr As String, tCode
    
    'Find Color Codes in the Column
    shName = "Sheet1"
    Set dSh = ThisWorkbook.Sheets(shName)
    Call Load_Dict_Color(shName, 1, 100)
    
    'Add Color Codes as Sort Key
    dSh.Sort.SortFields.Clear
    For Each gKey In gDictColHdr.Keys
        dSh.Sort.SortFields.Add(Range("A:A"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = gKey
    Next
    
    'Apply Sort
    With dSh.Sort
        .SetRange Range("A:A")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

'Build Color Array
Sub Load_Dict_Color(shName As String, dCol As Double, dLastRow As Double)
    Dim dSh As Worksheet, dRow As Double
    Dim tSect As String, tHdr As String, tCode
    
    'Process Till Last Column in DataSheets
    Set dSh = ThisWorkbook.Sheets(shName)
    Set gDictColHdr = New Scripting.Dictionary
    
    'Get Color codes till end of Row
    For dRow = 1 To dLastRow
        tCode = dSh.Cells(dRow, dCol).Interior.Color
        If gDictColHdr.Count = 0 Then
            gDictColHdr.Add tCode, tCode
        Else
            If gDictColHdr.Exists(tCode) = False Then
                gDictColHdr.Add tCode, tCode
            End If
        End If
    Next dRow

End Sub

Note: Make sure to add reference to “Microsoft scripting Runtime” from Excel VBA Editor -> Tools Menu-> References

Also, the above code does not find the last row in the Excel sheet. It will only consider color codes from rows 1 to 100. Make sure to change this in your coding accordingly.