How Can I Color Every Other Row in an Excel Spreadsheet?

ScriptingGuy1

Hey, Scripting Guy! When I create a new Excel spreadsheet, I’d like to color every other row in that spreadsheet. How can I do that?

— TM

Hey, TM. We know a lot of you have been wondering, “Gee, what’s the matter with the Scripting Guy who writes that column? He just doesn’t seem like his usual, jolly old self these days.” Sadly, that’s true. And there’s a reason for that: while in Orlando for TechEd 2007, the Scripting Guy who writes this column had both his confidence and his ego shattered in one crushing blow.

At one point during the festivities, the Scripting Guy who writes this column left the Scripting Guys’ booth in an attempt to find a place where the dynamic duo could store a box for a few hours. (How hard could it be to find a place to store one little box in a building the size of the Orlando Convention Center? Surprisingly hard. And yet, nowhere near as hard as it was trying to get that one little box back out of storage.) When the Scripting Guy who writes this column returned from his quest, Scripting Guy Jean Ross was busy chatting with someone. “Oh,” said this someone as he returned to the booth. “This must be the other Scripting Guy.”

The other Scripting Guy!?!? The Scripting Guy who writes this column single-handedly invented the Script Center, for goodness sake! How many Script Centers do you suppose Scripting Guy Jean Ross has invented? That’s right: zero. And yet Scripting Guy Jean Ross – who, we might point out, isn’t even a guy – has somehow become the Scripting Guy. And the Scripting Guy who writes this column? He has somehow become the other Scripting Guy.

The other Scripting Guy. Was George Harrison the “other” Beatle to John and Paul? Was Chico the “other” Marx Brother to Groucho? Was – well, never mind. You know what we mean.

Of course, people are truly defined by how they react to adversity. The Scripting Guy who writes this column reacted to adversity by getting very depressed and withdrawing into his office, his life pretty much over. What’s that? Write a really good column, one that, say, colors every other row in an Excel spreadsheet. You say that will show Jean Ross who the real Scripting Guy is? You know what? You’re right: that would show Jean who the real Scripting Guy is, wouldn’t it?

On the other hand, that sounds like a lot of work, and it sounds kind of hard, to boot. Sorry; we’ll pass on that.

Unless … Guess what? In rummaging through Jean’s desk, looking for a … paperclip … we just happened to run across a script that can color every other row in a spreadsheet. The other Scripting Guy? Not anymore, Jean Ross:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
For i = 1 to 5
    If i Mod 2 = 0 Then
        Set objRange = objExcel.ActiveCell.EntireRow
        objRange.Cells.Interior.ColorIndex = 37
    Else
        Set objRange = objExcel.ActiveCell.EntireRow
        objRange.Cells.Interior.ColorIndex = 36
    End If
    objWorksheet.Cells(i,1) = i
    intNewRow = objExcel.ActiveCell.Row + 1
    strNewCell = “A” &  intNewRow
    objExcel.Range(strNewCell).Activate
Next

OK, so how does this script work? (Good question; unfortunately, Jean didn’t seem to leave an explanation lying around anywhere. We’ll have to look through her purse the next time she goes to the bathroom.) As near as we can tell, the script starts out by creating an instance of the Excel.Application object, and then sets the Visible property to True; that gives us a running instance of Excel that we can see onscreen. Jean then – um, we then use the Add method to add a new workbook, then use this line of code to bind to the first worksheet in the workbook:

Set objWorksheet = objWorkbook.Worksheets(1)

That was easy enough, wasn’t it? OK. In our sample spreadsheet, we’re going to put data in the first five rows; therefore, we set up a little For Next loop that runs from 1 to 5:

For i = 1 to 5

Inside that loop, the very first thing we do is execute this line of code:

If i Mod 2 = 0 Then

What does this line of code do? Well, it … um, it – hold on second, we need to go ask Jean a question …..

OK, we’re back. As we were saying – and as any real Scripting Guy knows – the Mod function returns the remainder of a division problem. All we’re doing here is dividing the counter variable i by 2 and then checking to see if the remainder is 0. Why do we do that? Just a second … we do that as a way to keep track of alternating rows. Odd-numbered rows (1, 3, 5, 7, etc.) will not have a remainder of 0; that’s because 2 doesn’t divide evenly into an odd number. Even-numbered rows, by comparison, will have a remainder of 0. Even-odd, even-odd, even-odd: a simple way to keep track of alternating rows.

So what if the remainder is 0? In that case we’re going to execute this block of code:

Set objRange = objExcel.ActiveCell.EntireRow
objRange.Cells.Interior.ColorIndex = 37

As you can see, we’re doing two things here. First, we’re creating a range object that encompasses all the cells in row 1. How do we know that this range object encompasses all the cells in row 1? Hey, even the Scripting Guy who writes this column knows the answer to that one. With this line of code we’re referencing the EntireRow property for the row containing the ActiveCell (the cell that currently has the cursor in it). By default, any time you programmatically create a new spreadsheet the cursor gets placed in cell A1. And because cell A1 is in row 1, then that must mean we’re selecting all the cells in row 1.

In the second line of code we then set the background color (Interior.ColorIndex) of all the cells in row 1 to a light yellow. How did we know that the value 37 would result in a light yellow? That’s also an easy one: for some reason, Jean Ross has all the Excel color values tattooed to her forearm. We just waited until she rolled up her sleeves and then took a quick peek. If you can’t see Jean’s forearms from where you are, don’t panic: there is an alternative. For more information, see this Office Space article.

Now, what if the remainder isn’t 0? Well, in that case we could just leave the row uncolored. But that’s what someone like Scripting Guy Jean Ross would do. A real Scripting Guy would color the odd-numbered rows a light blue, using this block of code:

Set objRange = objExcel.ActiveCell.EntireRow
objRange.Cells.Interior.ColorIndex = 36

After coloring the row the appropriate shade, we then assign the value i to the cell in column A of the current row:

objWorksheet.Cells(i,1) = i

That’s no big deal; we just do that to make sure our spreadsheet has some data in it. We then run this block of code:

intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = “A” &  intNewRow
objExcel.Range(strNewCell).Activate

Why this block of code? Well, according to Jean, programmatically adding data to a new row doesn’t cause the active cell to change; if we don’t explicitly make a change then cell A1 will remain the active cell forever and ever. That wouldn’t be very good; if that happened we’d end up doing nothing more than changing the background color of row 1 over and over again. Therefore, we do the following instead.

First, we assign the value of the active cell’s Row plus 1 to a variable named intNewRow. Why “plus 1?” Well, let’s assume that the active cell is currently in row 1; that means we need to move the active cell to row 2. And because 1 + 1 is – we’ll be right back; hey, Jean, what’s 1 + 1? Anyway, because 1 + 1 is 2, well ….

We then combine the new row number with the letter A, assigning that value to a variable named strNewCell:

strNewCell = “A” &  intNewRow

Needless to say, the first time through the loop that will make strNewCell equal to A2. That means that we can then call the Activate method and make cell strNewCell (A2) the active cell:

objExcel.Range(strNewCell).Activate

From there we loop around and repeat the entire process with the next row in the worksheet.

When all is said and done we’ll end up with a worksheet that looks remarkably like this:

And we did it without any help from Jean Ross.

Pretty much.

By the way, we should point out that we have nothing against Scripting Guy Jean Ross; heck, some of our best friends are named Scripting Guy Jean Ross. It’s just that we don’t see what Jean has done to merit the title of the Scripting Guy. OK, granted, in the first year after she joined the team our page views more than doubled; that’s definitely impressive. And sure, Dr. Scripto’s Fun Book, which was one of the most sought-after giveaways at TechEd, was entirely her idea. And, fine: she’s the only Scripting Guy capable of keeping the Script Center going from day-to-day. But other than that ….

ScriptingGuy1

Follow