Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?
|
#2
|
|||
|
|||
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:
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I set up a excel spreadsheet to auto sort data in order | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Can I sort excel spreadsheet data by fill color of cells? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Importing Data From Another Spreadsheet | Excel Discussion (Misc queries) |