# 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?

Steve

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

Order1:=xlAscending, Orientation:=xlTopToBottom

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

Have fun
Dave

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 ?

Dave