Every now and then, you encounter a table where color is supposed to mean something. In this table, each color represents a certain attribute (red = below target, yellow = in danger). If you're thinking that tagging records with a color is stupid, I agree. But we live in a world full of goofballs that think it's clever to do things like this.
The problem here is that tagging records with just a color makes it difficult to find and consolidate the like-colors (especially in Excel 2003 where, unlike Excel 2007, there are no built-in tools that allow you to easily sort by color).
But with a little VBA, there is a way to sort by color in Excel 2003.
Step 1 – Create Your Custom Functions
- Press Alt-F11 on your keyboard to get to the Visual Basic Editor.
- Go to the menu and select Insert -> Module.
- Paste this bit of code into the empty module.
Function GetBackgroundColor(MyRange As Range)
GetBackgroundColor = MyRange.Interior.ColorIndex
End Function
Function GetFontColor(MyRange As Range)
GetFontColor = MyRange.Font.ColorIndex
End Function
Step 2 – Apply your Custom functions
- Create a new column and call it Color Index
- In your new column, enter the Get Color function needed. In this case, I need to get the color index for the cell background color, so I will use the GetBackgroundColor function.
Step 3 – Sort by the Color Index Column
- Sort your data table by the newly created Color Index column.
There you have it – Sorting by color in Excel 2003. As I mentioned before, Excel 2007 has a better (built-in) solution. To learn how to sort by color in Excel 2007, check out Debra's Post.
I'd like to think that not many of you will need this Excel 2003 tip, but I know that some companies will keep you their folks on Excel 2003 for another 2-3 years.
1.Select Cell B1
2.Define a Name (CTRL+F3) called ColorNum
=GET.CELL(63,OFFSET(Sheet2!B1,0,-1))
or
=GET.CELL(63,Sheet2!A1)
3.Select a cell and type = ColorNum
This will return the fill color of the adjacent cell
Very cool Sam.