MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Old question.. can you sort by colour ?

Posted by Steve on January 08, 2001 9:03 PM

I've seen it asked before but can't remember if there was an answer. I want to sort by cell colour.. can it be done?


Posted by Dave Hawley on January 08, 2001 11:15 PM

Hi Steve

Here one way that seems to work, just change the ranges to suit. The "Range("ColorOrder")" is a named range that contains the colors in their order of priority i.e if you want blue as your first color the the first cell in ColorOrder should be blue.

You will need one blank column immediantley to the left of your data to sort.

Sub ColorSort()
Dim SortRange As Range
Dim ColorCount As Integer, i As Integer
Dim SortCount As Long, ii As Long
Dim Found As Boolean
Dim SortAll As Range

Set SortRange = Range("D2:D6")
ColorCount = Range("D2:D6").Rows.Count
SortCount = Range("ColorOrder").Rows.Count

For ii = 1 To SortCount
For i = 1 To ColorCount
Found = False
If SortRange.Cells(ii, 1).Interior.ColorIndex = _
Range("ColorOrder").Cells(i, 1).Interior.ColorIndex Then
SortRange.Cells(ii, 1).Offset(0, -1) = i
Found = True
Exit For
End If
Next i
If Found = False Then SortRange.Cells(ii, 1).Offset(0, -1) = 10000
Next ii
Set SortAll = SortRange.CurrentRegion

SortAll.Sort Key1:=Range(SortRange.Cells(1, 1).Offset(0, -1).Address), _
Order1:=xlAscending, Orientation:=xlTopToBottom

SortRange.Offset(0, -1).Clear
End Sub

Have fun

  • OzGrid Business Applications

Posted by Steve on January 09, 2001 6:52 PM

Well we gave it a go but it keeps stalling .. I can tell you that I am no were near the level of excel needed know how to do this.. but I think it may have something to do with either the ranges.. or the order of colours required...
any words of wisdom.. or easier ways to this????

Posted by Dave Hawley on January 09, 2001 9:19 PM

You will have to tell me where it's stalling ? and what you mean by stallin ?

If you like I could send you a working cop ?


  • OzGrid Business Applications

Posted by Steve on January 10, 2001 6:39 PM

A working copy would be great thanks.. sorry my terminology isn't that great either.. the macro runs then stops in a specific cell... another problem is that if it did work does it only work on one coloumn or can it do multiple columns & rows.. hope I don't confuse you too much.. your help has been appreciated!.