Hey, Scripting Guy! How Can I Change the Font Color in Office Excel If a Specified Condition is Met?

ScriptingGuy1

Hey, Scripting Guy! In an Office Excel spreadsheet, how can I change the color of the text if the value in column B minus the value in column A is greater than or equal to 10?

— EP

Hey, EP. You know, before the Scripting Guy who writes this column started writing this column, he vowed that Hey, Scripting Guy! would have a laser-like focus on helping people learn system administration scripting; Hey, Scripting Guy! would not be a forum for inane rantings and ravings that no one else cared about. Throughout the years has he managed to stick to that vow? Let’s put it this way: have you seen the commercial featuring the Rock’em Sock’em Robots? The Scripting Guy who writes this column this column has, and he finds this commercial very distressing.

Very distressing.

If you haven’t seen the commercial, two life-size Rock’em Sock’em Robots (made of metal, oddly enough) are duking it out in a boxing ring. After defeating the red robot the blue robot crashes through the gymnasium door, terrorizes a couple of people on the street, and then proceeds to attack a pickup truck, a fight he ends up losing.

So what’s the big deal here? Well, let’s be realistic here: the Rock’em Sock’em Robots would never terrorize innocent people, nor would they pick a fight with a pickup truck that was just sitting there minding its own business. The Scripting Guy who writes this column had Rock’em Sock’em Robots when he was a kid; the Scripting Son had Rock’em Sock’em Robots when he was a kid. In all that time those robots never once left the ring, nor did they ever attack anyone other than the other robot. A Rock’em Sock’em Robot would never do anything like that; they have too much class. This is an outrage and something needs to be done about it.

What’s that? Are we finished ranting and raving? Sure … for now. Why do you ask?

Oh, right: a script that can do a little math and then, as needed, change the text color in Microsoft Excel. Hey, all you had to do was ask:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)

i = 1

Do Until objWorksheet.Cells(i, 1) = ""
    intA = objWorksheet.Cells(i, 1)
    intB = objWorksheet.Cells(i, 2)
    If intB - intA >= 10 Then
        objWorksheet.Cells(i, 1).Font.ColorIndex = 3
        objWorksheet.Cells(i, 2).Font.ColorIndex = 3
    End If
    i = i + 1
Loop
According to EP he has a spreadsheet which – in simplified fashion – looks something like this:

As you can see, there’s a year (e.g., 2002) shown in column A and another year (e.g., 2004) shown in column B. Our task? Subtract column A from column B (2004 – 2002) and then, if the result is greater than or equal to 10, change the font color for that particular row. Can we do that? Assuming that we don’t have to stop for any further rants, you bet we can.
Our script starts in straightforward fashion: we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Excel that we can see onscreen. We then use these two lines of code to open the file C:\Scripts\Test.xls and bind to the first worksheet in the file:
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
Once that’s done we create a counter variable named i and assign it the value 1; we’ll use this variable to keep track of the current row in the worksheet.
Got all that? Good; now it’s time to do some real scripting. In order to keep matters as simple as we can, we’re assuming that the data begins in row 1, column 1; that is, there’s no header row. In addition, we’re assuming that there are no blanks rows in the data. Based on those assumptions, our next chore is to set up a Do Until loop that runs until we encounter a blank cell in column A; that’s what this line of code is for:
Do Until objWorksheet.Cells(i, 1) = ""
Remember, the counter variable i keeps track of the row for us. When our loop begins, that means we’re checking to see if cell row 1, column 1 is blank. If it is, then the script is finished. 
If it’s not we then take the value of cell row 1, column 1 and assign it to a variable named intA; needless to say, this variable represents the value of cell A1 (row 1, column 1). We then take the value of row 1, column 2 and assign it to a variable named intB; in turn, this variable represents the value of cell B1. That’s what these two lines of code do:
intA = objWorksheet.Cells(i, 1)
intB = objWorksheet.Cells(i, 2)
As soon as we have the values in columns A and B we can then use this line of code to determine whether or not the result of subtracting column A from column B is greater than or equal to 10:
If intB - intA >= 10 Then
If the value is not greater than or equal to 10 then we don’t do anything at all. But what if the value is greater than or equal to 10 (as it will be in row 3, when we subtract 1992 from 2004)? In that case, we execute these two lines of code:
objWorksheet.Cells(i, 1).Font.ColorIndex = 3
objWorksheet.Cells(i, 2).Font.ColorIndex = 3
What’s going on here? Well, in the first line we’re simply setting the Font.ColorIndex property for the cell in column A to red. (A value of 3 means red; for more information on available colors and their values, see this Office Space article.) And in the second line – that’s right, in the second line we’re setting the Font.ColorIndex property for the cell in column B to red. When all is said and done our spreadsheet should look a little something like this:

And there you have it.
Now, getting back to the Rock’em Sock’em Robots, the Scripting Guy who writes this column recommends that, as a way to register our collective disappointment, we all agree to boycott sushi restaurants. That’s a tough thing to do and a tough sacrifice to make. But it’s the only way these companies will learn.





Note. OK, sure, if you want to get picky the commercial has nothing to do with sushi restaurants; instead, it’s for a car company. But the Scripting Guy who writes this column can’t boycott cars: he lives in Seattle, a city that’s pretty much devoid of mass transit. (In Seattle we don’t ever build mass transit systems; instead, we just vote on mass transit systems, year after year after year.) By contrast, sushi restaurants are something he can boycott, and without any inconvenience to himself whatsoever. And isn’t that the best kind of boycott?





        

                

        
		
        
        


            

            

                
                    ScriptingGuy1                
                
            
            


                
        

            Follow