How can I sort a spreadsheet of data by colour?
I have 4 colours on my spreadsheet, how do I sort the data by the colour?
|
How can I sort a spreadsheet of data by 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 Quote:
|
All times are GMT +1. The time now is 01:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com