Ok people. Repeat after me – "A color is not data".
Every so often I get a spreadsheet from someone who thinks that colors play a major role in data management. You know – those people who happily say "I tagged those records with Yellow". Oh really? I don't think my version of Excel has a "Yellow" data type.
Anyway, I recently got a dataset from a client who included this gem of a column. Isn't it pretty? Apparently, Gray means 'In Progress', Red means 'Past Due', and Green means 'Complete'. I guess the goal here was to save typing a status in a new column?
In any event, my task was to get this monthly feed and load it into a SQL server database. So my first step was to create a User Defined Function that would allow me to identify the Font Color of a range through a color index. I entered this code into a standard Module:
Function GetFontColor(MyRange As Range)
GetFontColor = MyRange.Font.ColorIndex
End Function
At this point, I could use my function to simply point to a cell and get the index number of the font color.
Once I identified the appropriate color-to-status mapping, I created a simple table that translated the color index into a status.
Finally, I wrapped my GetFontColor Function in a VLookup to get the Status name.
Tada! Now I have a way to easily extract the real data from this lame coloring system.
A couple of notes:
You can't use this to pull out the color index of any cell that gets its color from Conditional Formatting. This is because the FormatCondition object is not tied to the Range Object. I've seen some code to pull out conditional color, but it gets pretty nasty.
If you want to pull out the background color index instead of the font color index use this:
Function GetFontColor(MyRange As Range)
GetFontColor = MyRange.Interior.ColorIndex
End Function
The use of colour indices makes it difficult (for users) to change the colours. Wouldn't it be better to get users to simply set the font colours in the cells of the lookup table, then VBA can figure out the colour index from that?
Flagging UDF's a volatile is needed too isn't it?
And as another option we could use XLM4 GET.CELL as a name, if avoiding code is preferred.
Although I avoid colour coding altogether
"Ok people. Repeat after me – "A color is not data"."
I need to quote that in my email sig. Maybe post it on a few boards around the office.
I had to send another email on Monday trying to explain how a comments section can't make a load decision.
Another approach you could have used instead of all that worksheet manipulation is to have let a macro do all the work. To use the following macro, you would simply select one of the colored dates, run the macro, type the description you want to put into Column C for that color and hit the Enter key (or click the OK button). True, you would have to do this for each color, but I think it would involve less overall work than what you went through with your worksheet manipulations. And, if there were enough different colors, you could alway attach the macro to a temporary CommandButton placed on the worksheet to make things a little easier. Anyway, here is the macro for those who might need such a thing in the future…
Sub IndentifyColors()
Dim Description As String, FirstAddress As String, Cell As Range
Description = InputBox("What is the text description for this color?")
Application.FindFormat.Font.ColorIndex = Selection(1).Font.ColorIndex
Set Cell = Columns("B").Find("*", SearchFormat:=True)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.Offset(0, 1).Value = Description
Set Cell = Columns("B").Find("*", After:=Cell, SearchFormat:=True)
Loop While Not Cell Is Nothing And Cell.Address <> FirstAddress
End If
End Sub
Another, low-tech, method is to sort or filter the data by font color (or cell color) (available in Excel 2007), then add a new column and enter the new description value and fill down for each category.
You've got to cut us code-phobes a break every once in awhile
Just to be perfectly clear about my wording, when I said this in my first message…
"True, you would have to do this for each color"
I meant for each DIFFERENT color, not for each cell with colored text; so, for the example from the blog, my macro would only have to be run 3 times, once for each color that was used… the macro takes care of finding all the similarly colored text and applying the description to them.
Actually, color is data.
color = information = data.
The problem here is that there is no distinction between the model and the view.
kzutter: A color can MAYBE be considered an attribute that can be used as meta-data. But that's as far as I go!
piggy: doesn't the colour of the bacon tell you whether its ready to eat or not? That sounds like more than metadata to me!
But Mr. Pig: why would Excel let us sort by color if it isn't data?!!! Excel is always right, isn't it?
This doesn't help any when people can seem to color things consistently. "Yes, the cell is orange, but it's not the same orange…" is a conversation I've had recently.
Dont going saying that sort of thing to visual management types!
Conditional formatting is the mood ring for Excel. It works and if you are 30 past due you are red. It is emphasis. In word text is data where bolding and underlining are emphasis.
Just relax.
Coloring data is not useful to those who have trouble distinguishing colors. Excessive use of colors is confusing on spreadsheets.
I wrote an add-in to assist in determining worksheet colors. It identifies… cell interior, cell pattern, font, border and sheet tab colors and does so for normal or conditionally formatted colors.
It only works in Excel versions prior to xl2007 (god bless Microsoft for their ability to force upgrades).
Colors displayed on the cell right-click menu – see a picture here… http://i258.photobucket.com/albums/hh247/James_Cone/DetermineColorsPicture.jpg
or
download it here (free)… http://excelusergroup.org/media/p/326.aspx
useful stuff. I always weep when people send these to me – now I don't have to!