Wednesday, March 12, 2008

How to sort Excel cells by color...

Finally figured this one out. Although a workaround, it seems to work fine. This is possible by inserting an extra column in the sheet and then using a user defined function. The function will return a number indicating the colour of the cell used in that particular cell (or row). You can then use the normal (Data > Sort) command using this new column as your first sorting column.
Copy and paste the following code to a new VBA module created in the excel file you are working in:

Option Explicit

Public Function ColourSorting(ByVal rgeCell As Range, _
ByVal bBackGround As Boolean, _
ByVal bText As Boolean) As Integer

If bBackGround = True Then ColourSorting = rgeCell.Interior.ColorIndex
If bText = True Then ColourSorting = rgeCell.Font.ColorIndex
If ColourSorting < coloursorting =" 0"
End Function

More detailed stuff on this site: