How Can I Determine the Background Color of a Spreadsheet Cell?

ScriptingGuy1

Hey, Scripting Guy! We have an Excel spreadsheet that tracks the status of all our projects. In that spreadsheet the status for a project is indicated by a color assigned to a cell. How can I use a script to determine the color of these cells?

— AE

Hey, AE. Just a second; we need to catch our breath. Sorry. Last night the Scripting Guy who writes this column and the Scripting Son went to the gym. As they sat down on the exercise bikes the Scripting Son set’s Dad’s difficulty level to 20, twice what they normally do. “Don’t worry, I’ll set it back,” he said. “There’s no way an old guy like you could do level 20, not even for one minute.”

Of course, a mature, grown-up adult would have replied, “Thank you, son. After all, one should never take foolish risks simply to win a dare or otherwise ‘prove oneself.’” Unfortunately, though, there weren’t any mature, grown-up adults around at the time. “Listen,” said the Scripting Dad. “I’ll not only do level 20 for one minute, I’ll do level 20 for five minutes. And I’ll do it fast, too.”

To his … credit … he did just that: he rode the bike , at level 20, for 5 minutes, fast. At the end of those 5 minutes, he was sweating hard and breathing even harder. This morning, a good 13 or 14 hours later, he’s stopped sweating. But he’s still breathing hard.

And sure, maybe that was kind of a dumb thing to do. But the important thing is that the Scripting Dad put the Scripting Son in his place. Which he’ll remind him of, assuming that he ever catches his breath long enough to speak again.

Fortunately, it doesn’t take a lot of oxygen to write a Hey, Scripting Guy! column. (Well, assuming the Scripting Editor hasn’t set the keyboard difficulty level to 20, that is.) Hold on a second … OK, here goes:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
i = 2
Do Until x = 1
    If objWorksheet.Cells(i,1) = “” Then
        Exit Do
    End If
    intColor = objWorksheet.Cells(i,2).Interior.ColorIndex
    Select Case intColor
        Case 3 strStatus = “Behind schedule”
        Case 4 strStatus = “Project complete”
        Case 6 strStatus = “Project on schedule”
        Case Else strStatus = “No information”
    End Select
    Wscript.Echo objWorksheet.Cells(i,1).Value & ” — ” & strStatus
    i = i + 1
Loop

Whew; does it seem hot in here to anyone else? Oh, well. Before we go any further we should note that, without knowing exactly what your spreadsheet looks like, AE, the best we can do is provide a generic answer to your question. For our sample script we’re using a simple little spreadsheet that looks like this:

As you can see, the spreadsheet merely lists the project names in column A and the status of each project in column B. In this sample spreadsheet status can be interpreted like so:

As for the script itself, we start out in pretty straightforward fashion, creating an instance of the Excel.Application object and then setting the Visible property to True; that gives us a running instance of Excel that we can see onscreen. We then use these two lines of code to open the file C:\Scripts\Test.xls and bind to the first worksheet in that file:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)

Wow; most days typing in two lines of code wouldn’t tire us out like that. (Sure, it tires us out some, but usually not this much.) But we’ll solider on the best we can. With our spreadsheet open we next create a counter variable named i and assign this variable the value 2; we’ll use i to keep track of our current row in the spreadsheet. That brings us to this block of code:

Do Until x = 1
    If objWorksheet.Cells(i,1) = “” Then
        Exit Do
    End If

    intColor = objWorksheet.Cells(i,2).Interior.ColorIndex
    Select Case intColor
        Case 3 strStatus = “Behind schedule”
        Case 4 strStatus = “Project complete”
        Case 6 strStatus = “Project on schedule”
        Case Else strStatus = “No information”
    End Select
    Wscript.Echo objWorksheet.Cells(i,1).Value & ” — ” & strStatus
    i = i + 1
Loop

What we’ve done here is set up a Do Until loop that’s designed to run until x is equal to 1; of course, because we never do anything to make x equal to 1 that means that the loop is really designed to run forever and ever. Fortunately, there’s a, well, loophole that we can use to break out of this never-ending story (something we’ll discuss in a minute). But the loop isn’t all that interesting; what’s interesting is what we do inside that loop.

Of course, the very first thing we do inside the loop – depending on the circumstances – is exit the thing. Remember the loophole we told you about? That’s what this is for:

If objWorksheet.Cells(i,1) = “” Then
    Exit Do
End If

What we’re doing here is checking the value of cell A2. How do we know it’s cell A2? Because we’re looking at cell row i, column 1, and, at the start of the loop, i is equal to 2. The cell located at row 2 column 1 is more-commonly known as cell A2. If the value of cell A2 is equal to nothing (“”) we call the Exit Do statement to exit the loop. Checking for a blank cell is how we know whether or not we’ve reached the end of the data. Because we have no blank rows in the spreadsheet a blank cell in column A can only mean one thing: there’s no more data to read.

Let’s assume, however that there is a value in cell A2. In that case, we use this line of code to determine the color of cell B2 (because we track status in column B) and then store the integer value of that color in a variable named intColor:

intColor = objWorksheet.Cells(i,2).Interior.ColorIndex

Nothing too fancy here: we simply grab the value of the cell’s Interior.ColorIndex property. Like we said, this will always be an integer value: 3 represents the color red; 4 represents the color green; and 6 represents the color yellow.

After we’ve determined the color of the cell we then set up the following Select Case statement to interpret the integer value:

Select Case intColor
    Case 3 strStatus = “Behind schedule”
    Case 4 strStatus = “Project complete”
    Case 6 strStatus = “Project on schedule”
    Case Else strStatus = “No information”
End Select

Again, this shouldn’t cause you any undue problems. The first time through the loop (at least with the sample spreadsheet we showed you) intStatus will be equal to 6; that’s because the cell has a yellow background. In turn, that means we set the value of a variable named strStatus to Project on schedule. That’s what this line of code does:

Case 6 strStatus = “Project on schedule”

With the value of strStatus set we can then echo back the name of the project (found in cell A2) plus the project status (strStatus):

Wscript.Echo objWorksheet.Cells(i,1).Value & ” — ” & strStatus

Yes, that was easy, wasn’t it? And trust us: no one is happier about that than the Scripting Guy who writes this column.

After echoing back status information for Project 1 we increment the value of i by 1; that means that, the second time through the loop, we’ll be working with row 3 in the spreadsheet. This process continues until we encounter a blank row; at that point the script automatically ends.

And what kind of output can we expect to get here? Output like this:

Project 1 — Project on schedule
Project 2 — Behind schedule
Project 3 — Project complete
Project 4 — Project complete
Project 5 — Behind schedule

We hope that helps, AE. If not, write back to us – tomorrow. We have a feeling we won’t be doing much of anything today.

And no, that has nothing to do with being tired; in fact, we’ve fully recovered by now. We just don’t feel like working today

ScriptingGuy1

Follow