Figuring Out What Cell is Highlighted in Excel

Posted on February 7, 2008 by LJ Earnest
Categories: Techniques

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
   Else
      IsColor = 1
   End If
End Function

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.