Macro 41: Apply Alternate Color Banding
Color banding is an effect where each row of a data set is colored in alternating shades (see Figure
4-5). You would typically apply alternating row colors to reports you distribute to people who need
to review each row of data. Color banding makes the data a little easier to read. This macro allows
you to automatically apply alternating colors to each row in the selected range.
Figure 4-5: Color banding helps make your data sets easier to read.
How it works
In this macro, we are essentially using two Range object variables. One of the variables captures the
scope of data we are working with, whereas the other is used to hold each individual cell as we go
through the range. Then we use the For Each statement to activate or bring each cell in the target
range into focus. When each row is in focus, we use the Offset property to evaluate the color index of
the previous row. If the color index is white, we apply the alternate green color index.
Sub Macro41()
‘Step1: Declare your variables.
Dim MyRange As Range
Dim MyRow As Range
‘Step 2: Define the target Range.
Set MyRange = Selection
‘Step 3: Start looping through the range.
For Each MyRow In MyRange.Rows
‘Step 4: Check if the row is an even number.
If MyRow.Row Mod 2 = 0 Then
‘Step 5: Apply appropriate alternate color.
MyRow.Interior.ColorIndex = 35
Else
Macro 41: Apply Alternate Color Banding 129
MyRow.Interior.ColorIndex = 2
End If
‘Step 6: Loop back to get next row.
Next MyRow
End Sub
1. We first declare two Range object variables. One, called MyRange, holds the entire target
range. The other, called MyCell, holds each cell in the range as the macro enumerates
through them one by one.
2. Step 2 fills the MyRange variable with the target range. In this example, we are using the
selected range — the range that was selected on the spreadsheet. You can easily set the
MyRange variable to a specific range such as Range(“A1:Z100”). Also, if your target
range is a named range, you could simply enter its name: Range(“MyNamedRange”).
3. In this step, the macro starts through each cell in the target range, activating each cell as it
goes through.
4. When a cell is activated, we determine if the current row is an even row number.
5. If the row number is indeed even, the macro uses the alternate green color index 35. If not, it
uses the color index 2.
6. In Step 6, the macro loops back to get the next cell. After all of the cells in the target range
are activated, the macro ends.
How to use it
The best place to store this macro is in your Personal Macro Workbook. This way, the macro is always
available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project
window, it will be named personal.xlb.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click personal.xlb in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
If you don’t see personal.xlb in your project window, it doesn’t exist yet. You’ll have to record a
macro, using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option
in the Record Macro dialog box before you start recording. This option is in the Store Macro In
drop-down list. Simply record a couple of cell clicks and then stop recording. You can discard the
recorded macro and replace it with this one.
