Photo by marklarson
I routinely receive spreadsheets at work for import into our system. This last week, I received one with a bunch of lecture titles, and the ones that were now inactive were colored yellow.
While this conveys something to the person looking at the spreadsheet, I needed to convert that into database entries. And database entries don’t translate ‘yellow’ into ‘no’.
I had to translate those yellows into ‘no’. My options were these: go through the spreadsheet and manually type in yes/no based on the color, or find some way to do it programmatically. Since there were hundreds of entries, I realized manual would take time and be inaccurate. So I researched it. I found a way to populate a column based on the color of another cell.
This following method was done in Excel 2003. I’m sure it could be done in other versions, you will have to extrapolate.
Within the Visual Basic editor, I made an entry in the Personal.XLS modules. It is important to make it there so that the function will be available to all of your spreadsheets. The code was simple:
Function IsColor(x As Range) As Boolean
If x.Interior.ColorIndex <> xlColorIndexNone Then
IsColor = 0
IsColor = 1
Next, in my spreadsheet, I made the call to the following formula: =IsColor(cell reference). This gave me the Boolean values I needed to import into my database.