Hey, Scripting Guy! In an Excel spreadsheet, how can I change the color of a row if there is a date stored a particular cell in that row?

— GL

Hey, GL. Some of you might have noticed that there was no Hey, Scripting Guy! column yesterday. Is that because the Scripting Guys are slacking off and shirking their responsibilities? Of course; after all, why would yesterday be different than any other day? However, the main reason there was no column yesterday is because yesterday was Memorial Day here in the U.S., and all Microsoft employees got the day off. (Yes, even those who don’t really deserve a day off.) Couldn’t one of the Scripting Guys have come in and published the column anyway? Sure, but at Microsoft we get very few days off, so we all decided to stay home while we had the chance.

As we noted, at Microsoft we don’t get very many days off, which means that we don’t really need to keep track of which days are work days and which days aren’t. (Well, most of us don’t; there’s at least one Scripting Guy, who shall remain nameless, who once came in to work on a holiday, and actually worked for about 4 hours before realizing that he was the only one in the building.) But if you (like GL) have spreadsheets that include dates, then you might be interested in a script that can highlight any rows where those dates occur. You know, a script like this one:

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

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
i = 1
Do Until objExcel.Cells(i, 1) = “”
    strValue = objExcel.Cells(i, 1)
    If IsDate(strValue) Then
        objExcel.Cells(i, 1).EntireRow.Interior.ColorIndex = 44
    End If
    i = i + 1
Loop

So how does this script work? Well, to begin with, we’re assuming we have a spreadsheet similar to this:

As you can see, this is a pretty simple little file. We have data in some of the cells in column A: a few dates, a number, a text string. Our task is to programmatically pick out the rows that have dates in column A, and then change the background color of those rows. Sound complicated? Trust us, it’s not.

We start out, as we so often do, by creating an instance of the Excel.Application object and setting the Visible property to True; that gives us a running instance of Excel that we can view onscreen. We then use these two lines of code to open the spreadsheet C:\Scripts\Test.xls and bind us to the first worksheet in that file:

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

Once that’s done we assign the value 1 to a counter variable named i; we’ll use this counter variable to keep track of our position in the spreadsheet. (In particular, we’ll use this variable to keep track of which row we happen to be on.)

At this point we’re ready to start changing row colors. We’ve configured our sample spreadsheet to ensure that there are no blank rows in between rows that contain data. Why did we do that? Because that makes it easy to know when we’ve finished checking all the rows that contain data: if column A in a row is empty (“”) that means that we’ve checked everything that there is to check. With that in mind, we set up a Do Until loop that runs until we encounter a blank cell in column A:

Do Until objExcel.Cells(i, 1) = “”

The first thing we do inside the loop is grab the value of cell i, 1; the first time through the loop, that will be row 1, column 1 (i, of course, being the variable that keeps track of the current row.) We then assign the value of that cell to a variable named strValue:

strValue = objExcel.Cells(i, 1)

Next, we use VBScript’s IsDate function to determine whether or not that value is a date:

If IsDate(strValue) Then

If IsDate returns False then we aren’t dealing with a date after all; therefore, we simply increment the value of i by 1, then loop around and repeat the process with the next row in the spreadsheet. (And yes, IsDate relies on your Regional and Language settings when determining if a given value is a date. Just something you should keep in mind.)

OK, but what happens if IsDate comes back True? Well, that can mean only one thing: there’s a date value in column A. In turn, that means we need to change the background color of this row, something we do using this line of code:

objExcel.Cells(i, 1).EntireRow.Interior.ColorIndex = 44

This is kind of an awkward-looking command, but it’s ultimately quite simple. What we’re doing here is selecting the current cell (cell i, 1), then extending the selection to encompass the entire row; that’s what the EntireRow property does. We then change the background color by setting the Interior.ColorIndex property to 44. When all is said and done, and when we’ve finished going through the entire file, that’s going to result in a spreadsheet that looks like this:

See how that works? If there’s a date in cell A then that row is going to be colored a … lovely … orangish-yellowish-brownish kind of color.

And there you have it, GL. Incidentally, if you’re trying to make holiday plans we have to confess that we don’t actually know the date of Darth Vader’s birthday; all we know for sure is that he was born in the year 41.9 BBY. And yes, that is significant; after all, the year 42 BBY is known as Year 7 before the Great Resynchronization and Year 958 after the Ruusan Reformation. The year 42 BBY is also the year Gilead Pellaeon entered the Raithall Academy and the same year that Omo Bouri died. (Bouri, of course, is best known for negotiating the Treaty of Trammis in 124 BBY.)

And no, we don’t have the slightest idea what any of that means. Fortunately, though, not having any idea what we’re talking about has never been, and never will be, a deterrent for Hey, Scripting Guy!