Hey, Scripting Guy! How Can I Change the Value and Color of a Spreadsheet Cell Based on the Sum of Other Cells in That Spreadsheet?
Hey, Scripting Guy! I’m new to Excel, and I was wondering how to change the color of a cell if specific requirements are met. I have a spreadsheet that has three columns of numbers; I’d like to add the numbers in each row and, if they total 100, set the text of a fourth column to “Yes” and color that cell green. If the numbers don’t total 100 I’d like to set the text of column 4 to “No” and color the cell red. Can you help me with that?
Hey, JR. And Happy International Workers Day! Although most Americans are too busy working to pay much attention, May 1st is International Workers Day, a “celebration of the social and economic achievements of the international labor movement.” The Scripting Guys are different; we’re never too busy working to miss out on anything.
Note. In all fairness we should point out that Americans don’t observe International Workers Day; instead we have our own holiday – Labor Day – which falls in September and is also designed to honor the “strength and esprit de corps of the trade and labor organizations.”
You know, now that you mention it, that is highly unusual for the US to do one thing while the rest of the world does another, isn’t it? Interesting.
At any rate, we hope that everyone out there has a very happy International Workers Day. And what if you don’t celebrate International Workers Day? That’s OK; after all, May 1st is also Law Day in the US, a day in which people are encouraged to “reflect on the role of law in the foundation of the country and to recognize its importance for society.” (Oddly enough, Law Day has never really caught on, at least not in comparison to holidays like, say, Christmas.) If that’s not enough, May 1st is the birthday of country singer Tim McGraw, and – according to the Love Foundation – May 1st is also Global Love Day.
And no, we really shouldn’t make any comments about Global Love Day. We’ll leave that to the reader’s imagination.
Needless to say, that’s all pretty exciting, but we’ve saved the best for last. In the past few weeks we’ve gotten a lot of questions on the same general theme: how can I add up values in Microsoft Excel and then change the color of another cell to reflect the sum of those values? We’ve been hanging on to these questions for a few weeks, waiting until May 1st, which also happens to be International Change the Background Color of a Cell in an Excel Spreadsheet Day. And now the big day has finally arrived. Happy International Change the Background Color of a Cell in an Excel Spreadsheet Day everyone:
Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls") i = 1 Do Until objExcel.Cells(i,1).Value = "" intTotal = objExcel.Cells(i,1).Value + objExcel.Cells(i,2).Value + objExcel.Cells(i,3).Value If intTotal = 100 Then objExcel.Cells(i,4).Value = "Yes" objExcel.Cells(i,4).Interior.ColorIndex = 4 Else objExcel.Cells(i,4).Value = "No" objExcel.Cells(i,4).Interior.ColorIndex = 3 End If i = i + 1 Loop
Let’s see if we can figure out how this script works. (Granted, that does sound like a lot of work, which isn’t really in the spirit of International Workers Day. On other hand, we’re not sure that the Scripting Guys actually qualify for anything called International Workers Day.)
To begin with, we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Microsoft Excel that we can see onscreen. As soon as we have that instance we use the Workbooks.Open method to open the file C:\Scripts\Test.xls; that’s what this line of code is for:
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Ah, good question: what does Test.xls look like? Well, right now it looks a little like this:
As you can see, there’s nothing very special here: just three columns of numbers. But don’t despair; in no time at all we’re going to give this script a complete makeover.
Well, OK, a semi-complete makeover.
After the spreadsheet is open we set the value of a counter variable named i to 1; we’re going to use this counter variable to keep track of our current row in the spreadsheet. We set the value to 1 because the data we want to work with begins in row 1. What if our spreadsheet has a header row, and the data doesn’t actually start until row 2? Beats us; wow, that’s a real problem, isn’t it?
Pardon? Just set the value of i to 2, to make sure that the script starts off in row 2 of the spreadsheet? That’s a great idea; thanks.
If only we’d thought of that ….
Our next step is to set up a Do Until loop that will let us loop through all the rows in the spreadsheet; as you can see, this loop is designed to continue until we encounter a blank cell in column A:
Do Until objExcel.Cells(i,1).Value = ""
Note. If you haven’t done much scripting with Excel, the notation Cells(i, 1) simply refers to cell A1. How do we get A1 out of Cells(i, 1)? Well, because i is equal to 1 this syntax points to cell row 1, column 1; on other words, cell A1. What if i was equal to 2? Beats us, we – oh, wait: we do know the answer to this one. In that case we’d be looking at cell A2: row 2, column 1.
Inside the loop, the first thing we do is add up the values of the three cells in row 1 (cells A1, B1, and C1); that’s what this line of code is for:
intTotal = objExcel.Cells(i,1).Value + objExcel.Cells(i,2).Value + objExcel.Cells(i,3).Value
Again, there’s nothing very fancy going on here: we’re simply grabbing the Value of each cell, adding those values together and then storing the data in a variable named intTotal. Which is just exactly the way they would have done it during the International Socialist Conference of 1904, the conference that pushed for the eight-hour workday.
Note. Interestingly enough, it was the American Federation of Labor that convinced labor organizations throughout the world to accept May 1st as International Workers Day. As a side note, having won that battle, and having ensured that employers could not force them to work more than eight hours a day or 40 hours a week, the average American now puts in a 46-hour workweek, and over one-third of all US workers work more than 50 hours a week, with most of those extra hours put in by choice.
And yes, the average would be higher; for better or worse, people who put in the number of hours that the Scripting Guys do tend to bring the overall figure down.
OK, so we’ve added the value of cells A1, B1, and C1; now what? Well, now we check to see if intTotal is equal to 100:
If intTotal = 100 Then
If intTotal is equal to 100 then we’ve hit the magic number; in turn, we execute these two lines of code:
objExcel.Cells(i,4).Value = "Yes" objExcel.Cells(i,4).Interior.ColorIndex = 4
In line 1 we’re simply setting the value of cell D1 (row 1, column 4) to Yes. In line 2, we’re setting the value of the Interior.ColorIndex property to 4. That might not sound particularly exciting, but it is: this is the part of the script where we change the background color of the cell. (As it turns out, the background color of a cell is dictated by the ColorIndex of the cell’s Interior object. Which, as far as we know, is a standard that was also decided upon at the International Socialist Conference of 1904.) In this case, we’re setting the background color to 4, which just happens to be the index number for green.
Note. How did we know that setting the ColorIndex to 4 would color the cell green? Hey, when you’re a Scripting Guy you need to have an encyclopedic knowledge of just about everything, be it the origins of International Workers Day or the index numbers for cell colors in Microsoft Excel.
Oh, and we also took a look at this Office Space article.
Now, what if intTotal doesn’t add up to 100? No problem; in that case we simply execute these two lines of code instead:
objExcel.Cells(i,4).Value = "No" objExcel.Cells(i,4).Interior.ColorIndex = 3
In line 1 we’re setting the Value of cell D4 to No, and in line 2 we’re setting the background color of the cell to red. That’s really all there is to it.
From there we increment the value of our counter variable by 1, then pop back to the top of the loop and repeat the process with the next row in the spreadsheet. When all is said and done, our spiffed-up worksheet should look a little something like this:
Which is just what JR was hoping it would look like.
That’s all we have time for today; after all, we have work to do. And yes, it does seem a little unfair that the Scripting Guys have to work both on International Change the Background Color of a Cell in an Excel Spreadsheet Day and on International Workers Day. But such is life; as Andy McInerney once noted:
“… the modern working class – the class of ‘free labor,’ whose exploitation is hidden by the wage system – is only several hundred years old. Although its exploitation is masked, it is no less brutal. Men, women and children are forced to work long hours in miserable conditions just to eke out a bare subsistence.”
Coincidentally enough, that passage is almost identical to the official job description for the Scripting Guy who writes this column. (To tell you the truth, he’s still not sure what possessed him to even apply for the job in the first place, let alone to take it.)
See you all tomorrow. And don’t forget, tomorrow is May 2nd, the day on which we celebrate the 814th anniversary of King Richard I of England giving Portsmouth its first royal charter. You won’t want to miss that!