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?

ScriptingGuy1

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?
— JR

Hey, JR. And Happy International Workers Day! Although most Americans are too busy working to pay much attention, May 1^st 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.

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 1^st 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 1^st is the birthday of country singer Tim McGraw, and – according to the Love Foundation – May 1^st 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 1^st, 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 = ""

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.

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.

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:

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 2^nd, the day on which we celebrate the 814^th anniversary of King Richard I of England giving Portsmouth its first royal charter. You won’t want to miss that!

ScriptingGuy1

Follow