software

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

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Dianequot; gt; wrote in message
...
gt; 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 lt; 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) = amp;HFFFFFF ThenGetWhite = NdxExit 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) = 0amp; ThenGetBlack = NdxExit 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..

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

Hope that helps.

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