Answer My Searches

Have you ever wanted to sort or filter by the background color of cells in Excel? There are no functions built into Excel to do such a thing, but you can still do it with this simple user defined function:

Function Color(MyRef As Range) As Long
Color = MyRef.Interior.ColorIndex
End Function


I found installing it a bit convoluted (at least in Excel 2003). First you want to make sure you get it into your personal workbook so it will always be available to you. But when I went to the Visual Basic editor in Excel, it wouldn’t show me my personal workbook.

What I did was record a simple macro in Excel, being sure to select to store it in the “personal macro workbook” option. Do something simple like type in a cell, and hit stop recording. Then go into the Visual Basic editor again, and you can see your personal workbook (PERSONAL.XLS). Place the function above in the same module as the macro you just recorded. You can delete the macro you recorded now.

To use the function you have to call it in a funny way. Thus to get the color of cell A3 you would type in a cell:

=PERSONAL.XLS!Color(A3)

And now you can get numeric values for the background colors in your cells! Yay!