Figuring Out What Cell is Highlighted in Excel

Posted on February 7, 2008 by LJ

Welcome to Simple Productivity Blog! You may want to subscribe to my RSS feed. This will allow you to receive updates in your RSS reader every time new content is posted here. If you want to receive updates in your email, click on the envelope in the upper right corner, or use the "Subscribe" link below the blog title.

Please take some time to check out the content on the site. You may comment on any article by scrolling down and using the form at the bottom of all articles. Thanks for visiting!

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.

Bookmark It