Hey, Scripting Guy! I have a Microsoft Excel spreadsheet I periodically analyze using a script. How can I change the color of a cell to red if the date stored in that cell has passed?

— DM

Hey, DM. We were going to start today’s column off with some clever and witty remark about red-letter dates, but we couldn’t actually think of one. Therefore, we’ll pretend that we don’t have time for such nonsense and instead get right down to business.

In order to keep the explanation focused on the task at hand, let’s assume you have a very simple little spreadsheet, one that consists of a series of dates (all in column A). Our simple little spreadsheet looks something like this:

What we want to do is create a script that can open this file, check the date in each cell and, if that particular date has already passed, change the background color of the cell to red. In other words, after the script finishes we want our spreadsheet to look like this:

So can we do this? Well, while our Scripting Guy no longer seems capable of using the English language, he’s at least able to write scripts every now and then:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)

x = 1
Do Until objExcel.Cells(x,1).Value = “”
    If CDate(objExcel.Cells(x,1).Value) < Date Then
        objExcel.Cells(x,1).Interior.ColorIndex = 3
    End If
    x = x + 1
Loop

All things considered it is a short little script, isn’t it? The script starts out by creating an instance of the Excel.Application object and then setting the Visible property to True. We then use the Open method to open the file C:\Scripts\Test.xls. All that simply opens up our spreadsheet and makes it visible on screen.

After our spreadsheet pops up we assign the value 1 to a counter variable named x; we’ll use this variable to keep track of the current row as we work our way through the spreadsheet. Next we, well, start working our way through the spreadsheet, setting up a loop that continues until we encounter a blank value in column A. That’s what this line of code does:

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

Inside our Do Loop we check to see if the value of the current cell is less than today’s date. We do that by taking the value of the current cell – objExcel.Cells.(x,1).Value – and using the VBScript CDate (character-to-date) function to ensure that the value is treated as a date. We then compare that value to the current date (which we can get by using the Date) function. If the cell value is less then the current date, then we use this line of code to change the interior (background) color of the cell to red:

objExcel.Cells(x,1).Interior.ColorIndex = 3

Got all that? If you’re not sure why we’re checking the value of objExcel.Cells(x,1) remember that x is a variable we created to keep track of the current row. Because x starts off with the value 1 what we’re doing the first time through the loop is checking cell (1,1): row 1, column 1. After making this first check we increment x by 1; thus the next time through the loop we’ll be checking cell (2,1): row 2, column 1. Etc.

Just that easy, just that quick. We guess you could say that…that…well, OK, we can’t think of anything to say at the moment. But you get the idea.