Sort Excel 2003 Ranges By Color

Sorting cells by interior (fill) color in Excel 2003 requires VBA. Unfortunately, it's hard to write appropriate code, since there are so many different scenarios where you would want to sort a range by color. It could be a single column, or a multi-column range with only certain cells shaded. So, many of the routines you find will simply insert a color number on the worksheet for you to sort on your own, or allow you to provide a sort order.

For example, OzGrid's color sort code lets you provide a sort order, and Chip Pearson's Sort by Color code returns the colorindex for the font or the cell background. In both cases, you sort the data yourself.

The following is my entry into the "sort by color" arena. It inserts the color index and also does the sorting! It makes a few assumptions, though. It assumes you have a range of cells you want to sort, with header row, with no other nearby data that you do not want to sort.

Sub SortByColor()
' sort a block of cells by color
Dim rng As Excel.Range
Dim cell As Excel.Range
Dim arrData As Variant
Dim i As Long

' exit if not a range
If TypeName(Selection) <> "Range" Then Exit Sub

Application.ScreenUpdating = False

' grab the current range and set up array
Set rng = Selection
ReDim arrData(1 To Selection.Cells.Count)

' loop through cells and make note of color
i = 1
For Each cell In Selection
  arrData(i) = cell.Interior.ColorIndex
  i = i + 1
Next cell

' insert range and put color numbers there from array
rng.EntireColumn.Insert xlShiftToRight
rng.Offset(0, -1).Value = Application.Transpose(arrData)

' sort based on color
rng.CurrentRegion.Sort Key1:=rng.Cells(1, 1).Offset(-1, -1), _
Header:=xlYes

' delete extra column
rng.Offset(0, -1).EntireColumn.Delete

Application.ScreenUpdating = True
End Sub

Select the leftmost column of cells you want to sort (not the entire column, just the cells with data you want to sort). The code loops through the selected cells and grabs the color index. A column is inserted to the left of the data, and the color index numbers are pasted. The selected range (along with nearby data) is sorted according to the color value in the new column, then the new column is removed.

The ideal situation for this code is if you have a worksheet with a range of data starting in row 1, with row 1 containing headers. Some or all of the rows are shaded with different colors, and you want to sort them by color.