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.
RELATED STUFF
- Choosing Quarters in Excel
- Using the Switch Function in Excel
- Running an Access Parameter Query from Excel
- Creating a Zoom Box in Excel
- Flipping a Range Upside Down
- Avoiding Data Cut-Off in Excel
- Excel Zoom Box Font Size Follow Up
- Excel Defunct Defaults
- Why FIND when you can SEARCH
- Getting Data from Previous Sheet
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.
Hi Sam,
could you please brief me how it works internally your formula…what does it mean “get.cell”