Old question.. can you sort by colour ?

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
Dave

• OzGrid Business Applications

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

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

• OzGrid Business Applications

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!.

Steve
revan@hays.com.au