Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Changing the Background Color of a Cell

If you’ve ever wondered whether you were getting old or not, here’s an easy way to check: if you can remember when you could actually buy a black-and-white TV, then you’re old. (And if you can remember actually having a black-and-white TV, even if you were just a kid at the time, well ….)

In other words, the modern world is a world of color, from color TVs to color printers; shoot, even ketchup can be had in different colors these days. And what that means, of course, is that to be considered cool and cutting edge you have to make use of color as well. In turn, that means you need to be able to change the background color of cells in your Excel spreadsheet. That’s an easy enough thing to do when you’re working in Excel; all you have to do is set the Pattern color for the cell. But how do you do that using a script? How can you programmatically change the background color of a cell? How can you avoid being labeled an old fogey still stuck in a black-and-white world? How can –

Listen, take it easy. In today’s column, we’ll tell you how to change the background color of a cell. And, because we’re feeling generous, we’ll toss in an extra color tip or two, for no additional charge.

No need to thank us. Just remember, though, that if anyone asks, none of the Scripting Guys ever had a black-and-white TV. Ever.

To set the background color of a cell you need to specify a value for the Interior property’s ColorIndex. As the name implies, the Interior property represents the inside of the cell. To color the outside of a cell, you can use the Borders property.

The ColorIndex must be set to an integer value between 1 and 56, with each number representing a different color. (Don’t worry; we’ll talk about how you can know which number represents which color.) For example, here’s a script that creates a new worksheet, adds values to cells A1 through D1, and then sets the background color of cell B1 to 44 (a sort of orangish yellow):

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = "A"
objExcel.Cells(1, 2).Value = "B"
objExcel.Cells(1, 3).Value = "C"
objExcel.Cells(1, 4).Value = "D"

objExcel.Cells(1, 2).Interior.ColorIndex = 44

What you’ll end up is a spreadsheet that looks like this:

Makes you wonder what that Michelangelo guy has that we script writers don’t have, doesn’t it?

The key line, of course, is this one, which specifies a value for the Interior.ColorIndex property:

objExcel.Cells(1, 2).Interior.ColorIndex = 44

That’s it: call that line of code, and you’ll have yourself a nice orange cell.

Of course, you’re not limited to setting the background colors on a cell-by-cell basis; you can also set the background color for an entire range of cells, all at the same time. For example, the following script enters values into cells A1 through D1, and then uses the UsedRange property of the worksheet to create a range consisting of all four of these cells. The script then sets the background color for each cell in the range using this line of code:

objRange.Interior.ColorIndex = 44

Here’s the complete script:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = "A"
objExcel.Cells(1, 2).Value = "B"
objExcel.Cells(1, 3).Value = "C"
objExcel.Cells(1, 4).Value = "D"

Set objRange = objWorksheet.UsedRange
objRange.Interior.ColorIndex = 44

Now, it’s nice to know that you can easily set the background color of a cell (or group of cells) to any number between 1 and 56; it would be even nicer to know what a cell would actually look like with a background color of 13 or 17 or 38. (Green? Red? Magenta?) To tell you the truth, we don’t know if these color indices are documented anywhere; however, it’s easy enough to create a script that can show you the numbers 1 through 56 and their corresponding color indices. In fact, here’s a script that does just that:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

For i = 1 to 14
    objExcel.Cells(i, 1).Value = i
    objExcel.Cells(i, 2).Interior.ColorIndex = i
Next

For i = 15 to 28
    objExcel.Cells(i - 14, 3).Value = i
    objExcel.Cells(i - 14, 4).Interior.ColorIndex = i
Next

For i = 29 to 42
    objExcel.Cells(i - 28, 5).Value = i
    objExcel.Cells(i - 28, 6).Interior.ColorIndex = i
Next

For i = 43 to 56
    objExcel.Cells(i - 42, 7).Value = i
    objExcel.Cells(i - 42, 8).Interior.ColorIndex = i
Next

When you run the script, you should get output similar to this:

As it turns out, 13 is a purple, 17 a shade of blue, and 38 a kind of pink. Like they say: pretty as a picture.

If you want to remove the background color for a cell, simply set the value of ColorIndex to the constant xlColorIndexNone (make sure you assign the constant the value -4142). Here’s a sample script that sets the background color for cell B1, pauses for three seconds, and then removes that color:

Const xlColorIndexNone = -4142

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = "A"
objExcel.Cells(1, 2).Value = "B"
objExcel.Cells(1, 3).Value = "C"
objExcel.Cells(1, 4).Value = "D"
objExcel.Cells(1, 2).Interior.ColorIndex = 44

Wscript.Sleep 3000

objExcel.Cells(1, 2).Interior.ColorIndex = xlColorIndexNone

Special bonus script

The ColorIndex property can also be used to change the font color for a cell. Want orangish-yellow text in cell B1? Then use a script similar to this, which assigns the value 44 to the cell’s Font.ColorIndex property:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = "A"
objExcel.Cells(1, 2).Value = "B"
objExcel.Cells(1, 3).Value = "C"
objExcel.Cells(1, 4).Value = "D"

objExcel.Cells(1, 2).Font.ColorIndex = 44