Excel VBA & Menu Option – Sort By Color
Excel has option to Sort a column based on these 4 items.
- Sort based on Value
- Sort based on Cell color or Background color
- Sort based on Font color
- 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.
- Choose ‘Sort & Filter’ in Home menu -> Editing Tab
- Select ‘Custom Sort’
- Click ‘Cell Color’ in ‘Sort on’
- Choose the color in Order, that has to be sorted first.
- Click add level & then choose the next color to be considered next.
- 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.