How Can I Assign a Background Color to Cells in a Spreadsheet and Then “Sum” Those Cells?
Hey, Scripting Guy! How can I assign a background color (based on a specified value) to a spreadsheet cell and then “sum” those cells?
— JC
Hey, JC. Thanks for the question. And yes, you heard correctly: in his last game the Scripting Son did indeed go 4-for-4, banging out three singles and a double. That outing raised his batting average for the season to an even .500 which, all things considered, isn’t bad at all.
Of course, as the Scripting Dad likes to remind him, a .500 batting average means that the Scripting Son still fails one out every two times he steps to the plate. Although come to think of it, failing only once every two tries is a way better percentage than the Scripting Dad has when it comes to writing this column. Hmmm ….
But you’re in luck, JC, and on two accounts. For one, this happens to be one of those rare occasions when we actually do have an answer to a question. For another, we have a number of other things we have to do today; that means we can’t spend as much time on meandering and meaningless anecdotes as we usually do. Instead, we’re going to cut to the chase and get right to the script.
Well, in just a minute, that is. Did you know that the phrase “cut to the chase” originated in Hollywood in the 1920s? In the movie world “cut to the chase” meant skipping past the boring parts of the film – like characters discussing their dreams and aspirations – and getting to the good stuff: the chase scene. As a general rule we don’t use the phrase “cut to the chase” in relation to Hey, Scripting Guy!; after all, if you skipped the boring parts there wouldn’t be much left to the column, would there?
With that in mind, here’s the scripting equivalent of a chase scene, a script that can assign a background color to cells in a spreadsheet and then “sum” those cells:
Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = TrueSet objWorkbook = objExcel.Workbooks.Add() Set objWorksheet = objWorkbook.Worksheets(1)
objExcel.Cells(1, 1).Value = “A” objExcel.Cells(2, 1).Value = “B” objExcel.Cells(3, 1).Value = “C” objExcel.Cells(4, 1).Value = “D” objExcel.Cells(5, 1).Value = “E” objExcel.Cells(6, 1).Value = “F” objExcel.Cells(7, 1).Value = “G” objExcel.Cells(8, 1).Value = “H”
objExcel.Cells(1, 1).Interior.ColorIndex = 7 objExcel.Cells(2, 1).Interior.ColorIndex = 8 objExcel.Cells(3, 1).Interior.ColorIndex = 9 objExcel.Cells(4, 1).Interior.ColorIndex = 10 objExcel.Cells(5, 1).Interior.ColorIndex = 7 objExcel.Cells(6, 1).Interior.ColorIndex = 7 objExcel.Cells(7, 1).Interior.ColorIndex = 8 objExcel.Cells(8, 1).Interior.ColorIndex = 10
i = 1
Do Until objExcel.Cells(i, 1).Value = “” intColor = objExcel.Cells(i, 1).Interior.ColorIndex
Select Case intColor Case 7 intSum = intSum + 5 Case 8 intSum = intSum + 10 Case 9 intSum = intSum + 15 Case 10 intSum = intSum + 20 End Select
i = i + 1 Loop
Wscript.Echo intSum
To begin with we should note that we took a few liberties here. For example, JC has a script designed to enter a specific value into a cell and then set the background color of that cell based on the value entered. We didn’t bother setting up a fancy system like that; instead, we simply threw some data into some cells and then arbitrarily changed the background color of each cell. But that’s OK: after you see how easy it is to change the background color of a cell, and how easy it is to “read” these background colors, well, you won’t find it too terribly hard to adapt this script as needed.
Which reminds us: did we mention where the term “cut to the chase” came from? We did? In that case, let’s cut to the chase and discuss how the script works. We start out – in truly dramatic fashion – by 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 on screen. We then use these two lines of code to create a new workbook and to bind to the first worksheet in that workbook:
Set objWorkbook = objExcel.Workbooks.Add() Set objWorksheet = objWorkbook.Worksheets(1)
Next we have several lines of code that simply enter values into cells A1 through A8. For example, this command sets the value of cell A1 (row 1, column 1) to the letter A:
objExcel.Cells(1, 1).Value = “A”
And once we’ve assigned values to the cells we then use code similar to this to (again, arbitrarily) change the background color of each cell:
objExcel.Cells(1, 1).Interior.ColorIndex = 7
You can see how easy it is to change the color of a cell: all you have to do is assign the appropriate color integer to the Interior.ColorIndex property. The only tricky part here is knowing which integers correspond to which colors; for example, what color is 7? Fortunately, you can find out everything you need to know about colors and color values by taking a look at our Office Space article on that very topic.
So where are we? Turns out we’re right here, with a spreadsheet that looks like this:
What we want to do now is sum up the cells in the spreadsheet. However, we aren’t going to do that by adding the spreadsheet values; obviously adding A plus B plus C plus etc. wouldn’t make much sense. Instead, we want to determine the background color of the cell, assign that cell a value based on the color, and then add those values. Confusing? Don’t worry; we’ll explain it all in detail. And when we’re done with that we’ll talk about our dreams and aspirations.
Before we start reading the individual cells we have a line of code that assigns the value 1 to a counter variable named i; we’ll use this variable to keep track of our position (row) in the spreadsheet. We then set up a Do loop that runs until it encounters a blank cell in column A (at that point we’re assuming we’ve run out of data):
Do Until objExcel.Cells(i, 1).Value = “”
The first thing we do inside this loop is determine the background color of the cell and then assign the integer value to a variable named intColor; that’s what we do here:
intColor = objExcel.Cells(i, 1).Interior.ColorIndex
And once we know the background color of the cell we can start doing a little math:
Select Case intColor
Case 7 intSum = intSum + 5
Case 8 intSum = intSum + 10
Case 9 intSum = intSum + 15
Case 10 intSum = intSum + 20
End Select
What we’ve done here is set up a Select Case statement based on the value of intColor. In addition, we’ve assigned numeric values to each of the colors used in the spreadsheet. For example, the color 7 (a kind of pinkish-purplish sort of thing) has been assigned the value 5; the color 8 (sort of a cyan … we think) has been assigned the value 10; etc. Inside our Select Case statement we increment another variable (intSum). How much do we increment this variable by? Well, that depends on the cell color. For example, suppose we have a pinkish-purple cell. In that case intColor will be equal to 7, our script will execute this line of code, and intSum will be incremented by 5:
Case 7 intSum = intSum + 5
Again, nothing too terribly complicated here. If intColor equals 7 then we assign a new value to the variable intSum: the current value of intSum plus 5. The first time through the loop intSum is equal to 0; because of that, when we encounter that pinkish-purplish cell in row 1, column 1 our equation will be 0 + 5 = 5. As a result, intSum will be equal to 5.
Once we exit the Select Case statement we increment our counter variable by 1, then loop around and repeat the process with the cell in row 2, column 1. After we’ve looped through all the cells the script echoes back the dramatic conclusion:
90
Well, OK. But trust us, it’s more exciting to see it in person than it is to read about it.
And that should do it, JC. Like we said, we have several other things to take care of this afternoon, so we’re going to have to cut today’s column short. We apologize that you didn’t get to hear more about the Scripting Son. But don’t worry: as usual, he’ll be playing baseball on into the month of August. No doubt you’ll get to hear plenty of stories before the season is done.
Light
Dark
0 comments