I have 4 colours on my spreadsheet, how do I sort the data by the colour?

See http://www.xldynamic.com/source/xld....r.html#sorting for a
working solution

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Diane" wrote in message
...
I have 4 colours on my spreadsheet, how do I sort the data by the colour?

Diane,

Try this...

Function ColorIndexOfCell(Rng As Range, _
Optional OfText As Boolean, _
Optional DefaultAsIndex As Boolean = True) As Integer
Dim C As Long
If OfText = True Then
C = Rng.Font.ColorIndex
Else
C = Rng.Interior.ColorIndex
End If
If (C < 0) And (DefaultAsIndex = True) Then
If OfText = True Then
C = GetBlack(Rng.Worksheet.Parent)
Else
C = GetWhite(Rng.Worksheet.Parent)
End If
End If
ColorIndexOfCell = C
End Function
Function GetWhite(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = &HFFFFFF Then
GetWhite = Ndx
Exit Function
End If
Next Ndx
GetWhite = 0
End Function
Function GetBlack(WB As Workbook) As Long
Dim Ndx As Long
For Ndx = 1 To 56
If WB.Colors(Ndx) = 0& Then
GetBlack = Ndx
Exit Function
End If
Next Ndx
GetBlack = 0
End Function

Then, in the newly created column, enter either of the following formulas:

If you want to sort by the Background color of the cell, use the formula
=ColorIndexOfCell(A1,FALSE,TRUE)

If you want to sort by the Font color of the cell, use the formula
=ColorIndexOfCell(A1,TRUE,TRUE)

Of course, you can add your other two colors to this and substitute white/black with the ones you want. Just be sure to use the ColorIndex values to get the colors you want.

If you don't want to do VBA and it's just a once in awhile thing, you can check out this link to sort your worksheet by using the menus..

http://www.digdb.com/excel_add_ins/s...ngth_color/#A3

Hope that helps.

Mike