Hey, Scripting Guy! I need a script that can read all the values in a spreadsheet and then change the background colors of the cells in that spreadsheet based on a range of values. For example, if the value is less than 20 the cell gets colored one color; if the cell is between 20 and 40 it gets colored another color; and so on. How do I do that?
Hey, ESW. For you weather junkies out there, yes, last night the Seattle area got hit with another mid-winter snow storm, something that must have dumped at least an inch, maybe even an inch and a half of snow on the house of the Scripting Guy who writes this column. Could it be the end of the world? Actually, people in this part of the country would have a much better chance of surviving the end of the world than they have of getting through a little bit of snow. (“Snow weenies” is the term often tossed in our direction. We’d take exception to that, but we can’t: there’s snow on the ground, which means we can’t do anything. And that includes taking exception to something.)
Of course, we know what many of you are thinking: “Oh my gosh, a whole inch and a half of snow! How will you poor Seattleites survive such a nightmare!?!” Well, we can’t speak for everyone in the Puget Sound region. However, we can tell you that the Scripting Guy who writes this column plans to survive the nightmare by lounging around the house, eating doughnuts, drinking coffee, and watching a lot of TV.
Oh, and, uh … working … from home.
Note. Does this mean that the Scripting Guy who writes this column is a snow weenie? Maybe. But, then again, he’s also sitting at home in pair of sweatpants and a sweatshirt while you non-snow weenies are all dressed up and hard at work.
Think about it.
To his credit, the Scripting Guy who writes this column originally did plan on trying to make it in to work this morning. As he was watching the snow fall, however, he noticed his neighbor attempt to drive up the hill. A moment later, he watched as that same neighbor come backing down the hill. The neighbor tried again; less than a minute later he was backing down into his driveway. After the neighbor’s third unsuccessful try at negotiating the hill the Scripting Guy who writes this column said to himself, “I think I’ll have another doughnut.”
And then, after that, he decided to work from home today.
Anyway, right now it’s snowing again (although the sound on the rooftop suggests that there might be little sleet mixed in) and the only thing on TV is some 30-year-old game show being shown on the Game Show Network. That means we might as well take a few minutes to see if we can write a script that can change the background color of a cell based on a range of values.
Well, how about that? Turns out that we can write just such a script:
Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls") Set objWorksheet = objWorkbook.Worksheets(1) Set objRange = objWorksheet.UsedRange For Each objCell in objRange If IsNumeric(objCell.Value) Then If objCell.Value < 20 Then objCell.Interior.ColorIndex = 3 ElseIf objCell.Value < 40 Then objCell.Interior.ColorIndex = 6 ElseIf objCell.Value < 60 Then objCell.Interior.ColorIndex = 45 End If End If Next
As you can see, this was actually a pretty easy little script to come up with. We start things off by creating an instance of the Excel.Application object and set
the Visible property to True; that gives us a running instance of Microsoft Excel that we can see on screen. 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)
At this point, we’re ready to have some real fun!
Oh, right: we have to finish today’s column first, don’t we? OK, let’s finish today’s column first. Then we’ll be ready to have some real fun!
Oh, right: the 2008 Winter Scripting Games don’t start until February 15th. OK, let’s finish today’s column, and then sit around until February 15th. And
then we’ll be ready to have some real fun!
In the meantime, we use this line of code to create an instance of Excel’s Range object, a range that encompasses all the cells in the spreadsheet that contain
data. (Hence the name UsedRange.):
Set objRange = objWorksheet.UsedRange
Why do we want a collection of all the cells in the spreadsheet that contain data? You got it: that makes it very easy to loop through all the cells and, if the
value of a cell warrants, change the background color.
To do that, we begin by setting up a For Each loop to loop through all the items in the range (that is, all the cells in the spreadsheet). Inside that loop we use
the IsNumeric function to see if the Value of the cell is even a number in the first place. If it is, we then use this block of code to determine the exact value,
and to see if the value falls in any of our specified ranges:
If objCell.Value < 20 Then objCell.Interior.ColorIndex = 3 ElseIf objCell.Value < 40 Then objCell.Interior.ColorIndex = 6 ElseIf objCell.Value < 60 Then objCell.Interior.ColorIndex = 45 End If
As you can see, in line 1 we check to see if the Value of the cell is less than 20. If it is, we then use this line of code to set the background color of the cell to
red:objCell.Interior.ColorIndex = 3
Note. How did we know that setting the Interior.ColorIndex property to 3 would change the background color of the cell to red? Well, you might say that
Well, OK; actually, that little birdie told us to read the Office Space article Changing the Background Color of a Cell. Either way, though, the background
Suppose that the Value of the cell is less than 20. In that case, the background color of the cell gets changed to red, and we automatically exit the If Then
block; as soon as VBScript encounters a true condition it exits an If Then statement. Now, what if the value isn’t less than 20? Well, in that case we check
the next condition: is the Value less than 40? If it is, then we change the background color to yellow (6). If it’s not, then we check our final condition: is the Value
less than 60? If it is, we change the background color to orange (45); if it’s not, we leave the cell color as-is. Regardless, we then zip back to the top of the
loop and repeat the process with the next cell in the spreadsheet.
When all is said and done we should have a spreadsheet that looks like this:
Beautiful, yet functional. Just like the Scripting Guys.
That should do it, ESW. As for the Scripting Guy who writes this column, he has a busy day ahead of him. For example, should he make himself a grilled
cheese sandwich for lunch, or should he walk over to Dairy Queen and get a hamburger? Boy, working at home is proving to be much harder than he thought
it would be.