How Can I Change the Background Color of a Cell Depending on the Date?
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?
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.
Note. This column is written by the same Scripting Guy who, a short time ago, found himself unable to think of a word that started with a letter K. (Yes, yes: kite, kitten, kid, kindergarten; he’s well aware – now – that millions of words start with the letter K.) At the rate his mind is deteriorating this column will soon consist of a question and then a big blank area where the answer was supposed to go.
Hey: who said that would be an improvement?!?
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 = “”
Note. Why do we do that? Well, for the sake of simplicity we’re assuming you have no blanks cells in column A. Therefore as soon as we encounter a blank cell we’ll know that we’ve run out of data.
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
Note. How did we know that setting the ColorIndex to 3 would give us a red background? We didn’t, at least not until we ran the script referenced here that shows us all the background colors and their corresponding values.
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.