Imagine being able to create a beautiful “work of art” such as this in Excel with just a few well-placed clicks of your mouse…

If you’ve read any of my previous articles, you’ll find this one somewhat less serious. I’m just not quite ready to give up my summer hiatus yet, so I thought we’d go with some innocent Excel VBA fun. I apologize to any of you who were hoping for something more applicable.

As you may know, anything you can do in Excel can be also be done using VBA code. One of the things you can do in Excel is change the color of a cell. I thought it would be easy, and mildly amusing, to write a short macro which changed the color of a cell and the cells around it, when you clicked on it.

Event Handlers

Before we start, I’d like to introduce the concept of “event handlers”. The idea is that you can write code which will execute whenever a certain event happens, e.g. user clicks a button, selects an item from a list, modifies the text in a cell, etc. We’ve actually written such event handlers in the Excel VBA articles 4 and 5, but only for the specific event which occurs when the user clicks on a button. In this case, the click event is associated with a specific object, a button control. Each type of user interface control has certain events associated with it, but even the Excel Workbook and Worksheet objects have events associated with them. We will write code for a couple of these events in this project.

Go ahead and open Excel, and its VBA Editor (click on Tools, Macro, Visual Basic Editor in the Excel menu). In the VBAProject Editor window, note that the folder “Microsoft Excel Objects” contains a worksheet object for each worksheet in your workbook, as well as a workbook object itself, called “ThisWorkbook”. Double-click on Sheet1 to open the code window for the first worksheet.

You’ll notice at the top of the code window are two dropdown lists. The first is a list of all objects associated with this code module. In this case, the only object you will see is “Worksheet”. (There is also an item called “General” which represents code you write which is not associated with a specific object.) If this were the code module for a UserForm, the first dropdown would list each control on the form as well as the “UserForm” object itself.

Your Own Event Handler

Select Worksheet from the first dropdown list. You will see that a subroutine called Worksheet_SelectionChange is created automatically. This subroutine is an event handler which will be called automatically by Excel whenever the user selects a different cell. If you take a look at the second dropdown list above the code window, you will see that it is now filled with a list of all events associated with a Worksheet object. (Activate, BeforeDoubleClick, BeforeRightClick, etc.) Select the Activate event from this list. Once again, an empty subroutine will be created automatically for the event, in this case Worksheet_Activate.

Notice also that the Worksheet_SelectionChange subroutine has a parameter called Target which is a Range object:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

(The topic of parameters is covered in Excel VBA Lesson 3) The “Target” parameter represents the cell which the user has just selected. Let’s add the following line of code inside the Worksheet_SelectionChange function:

Target.Interior.ColorIndex = 5

This line of code changes the color of the cell to blue. You’ve just written an event handler. Switch back to Excel and click on a few cells. Each cell you click on turns blue! Now hold down one of your keyboard arrow keys for more fun.

Squares

Before we get any fancier with changing colors, let’s use our Worksheet_Activate event handler to make our cells square instead of rectangular. You can set the width of all columns simultaneously using the Columns collection of the Worksheet object. Ditto for row heights and the Rows collection:

Private Sub Worksheet_Activate()
Columns.ColumnWidth = 1
Rows.RowHeight = 8
End Sub

The Activate event will occur anytime you switch to this worksheet from a different worksheet. Go back to Excel, click on a different worksheet, then click on Sheet 1 again. Voila! Square cells.

More Colors

For our next step, we’ll diversify our color-changing beyond blue. Instead of simply setting the cell’s ColorIndex property to 5, we’ll add one to it each time:

Target.Interior.ColorIndex = Target.Interior.ColorIndex + 1

The problem with this code, however, is that a cell initially has no color (unless you consider “clear” a color, as my kids do). The value of the ColorIndex property for a cell with no color is an Excel VBA constant called xlColorIndexNone, which corresponds to a large negative value. If you try to add one to this value and set the ColorIndex with it, Excel will give you an error message. So we’ll need to check for this value specifically. We also need to make sure that we do not exceed the maximum allowed ColorIndex value, which is 56:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' If the cell is currently uncolored, or ColorIndex is
' past the max of 56, start over...
If Target.Interior.ColorIndex = xlColorIndexNone _
Or Target.Interior.ColorIndex + 1 > 56 Then
Target.Interior.ColorIndex = 3 'skip 1 (black) and 2 (white)
Else
Target.Interior.ColorIndex = Target.Interior.ColorIndex + 1
End If
End Sub

Now you’ll find that each time you click on a cell it changes to a different color. But note that nothing happens if you click on the same cell twice in a row. Since that cell is already selected, clicking on it again does not cause the SelectionChange event to occur. You’ll need to click on a different cell each time.

Color the Neighbors

We can now take this one step further by changing the colors of some of the cells near the Target cell. So that we don’t have to repeat the color-changing code above multiple times, we’ll create a separate subroutine called ChangeCellColor, which will change the color of any specified cell. We can then call ChangeCellColor multiple times from Worksheet_SelectionChange. Instead of passing the Range object (Target) to this function, we’ll pass it the row and column numbers of the cell we wish to change. This is because we don’t have range objects representing the neighboring cells.

Private Sub ChangeCellColor(ByVal lRow As Long, ByVal iCol As Integer)
' If the cell is currently uncolored, or ColorIndex is
' past the max of 56, start over...
If Cells(lRow, iCol).Interior.ColorIndex = xlColorIndexNone _
Or Cells(lRow, iCol).Interior.ColorIndex + 1 > 56 Then
Cells(lRow, iCol).Interior.ColorIndex = 3
Else
Cells(lRow, iCol).Interior.ColorIndex = _
Cells(lRow, iCol).Interior.ColorIndex + 1
End If
End Sub

This is the same code we had above, but using Cells(lRow, iCol) instead of Target. We can call this function from Worksheet_SelectionChange by using the Range object’s Row and Column properties:

ChangeCellColor Target.Row, Target.Column

But let’s also add:

ChangeCellColor Target.Row + 1, Target.Column ' cell below
ChangeCellColor Target.Row - 1, Target.Column ' cell above
ChangeCellColor Target.Row, Target.Column + 1 ' cell to right
ChangeCellColor Target.Row, Target.Column – 1 ' cell to left

Now when you click a cell, you’ll color the cell as well as the cells immediately above, below, to the right, and to the left. However, we have to make sure we are not attempting to color an invalid cell. For example, if we click on a cell in Row 1, our code will attempt to change the color of the cell directly above, in Row 0. Excel will not be pleased (Run-time Error 1004!), so we will put the following two lines at the top of our ChangeCellColor subroutine:

If lRow < 1 Or lRow > 65536 Then Exit Sub
If iCol < 1 Or iCol > 256 Then Exit Sub

Final Changes

We’re almost finished! Next, so that we can color different cells different colors, we’ll add a third parameter called ‘Increment’ to ChangeCellColor. Instead of adding one to the existing color index each time, we can add a different value each time.

Private Sub ChangeCellColor(ByVal lRow As Long, _
ByVal iCol As Integer, ByVal Increment As Integer)
' Make sure we have a valid cell:
If lRow < 1 Or lRow > 65536 Then Exit Sub
If iCol < 1 Or iCol > 256 Then Exit Sub
' If the cell is currently uncolored, or ColorIndex is
' past the max of 56, start over...
If Cells(lRow, iCol).Interior.ColorIndex = xlColorIndexNone _
Or Cells(lRow, iCol).Interior.ColorIndex + Increment > 56 Then
Cells(lRow, iCol).Interior.ColorIndex = 2 + Increment
Else
Cells(lRow, iCol).Interior.ColorIndex = _
            Cells(lRow, iCol).Interior.ColorIndex + Increment
End If
End Sub

Lastly, let’s add the four cells which are two cells away diagonally from our target cell, and use different Increment values for different values:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ChangeCellColor Target.Row, Target.Column, 1
' Cells immediately next to target cell:
ChangeCellColor Target.Row + 1, Target.Column, 2 ' cell below
ChangeCellColor Target.Row - 1, Target.Column, 2 ' cell above
ChangeCellColor Target.Row, Target.Column + 1, 2 ' cell to right
ChangeCellColor Target.Row, Target.Column - 1, 2 ' cell to left
' Two cells away diagonally:
ChangeCellColor Target.Row + 2, Target.Column + 2, 3 ' lower-right
ChangeCellColor Target.Row + 2, Target.Column - 2, 4 ' lower-left
ChangeCellColor Target.Row - 2, Target.Column - 2, 3 ' upper-left
ChangeCellColor Target.Row - 2, Target.Column + 2, 4 ' upper-right
End Sub

Download the complete project here: MindlessFun.zip. If you’ve been following along, you might try turning off your gridlines by going to Tools, Options, View tab, and unchecking the Gridlines checkbox. If you have any questions, or suggestions for future articles, please feel free to drop me a line: thorpe@ntsource.com.