Excel VBA Macro Programming

Overview

Sometimes when you have many rows of data on your spreadsheet, it is difficult to read across them, particularly when you print them out or they are very long. A solution is to color alternate rows ‚ you may remember the old ‚“music line ‚½ paper that spreadsheets used to be printed on for just this reason.

When writing this routine, you can restrict the shading to one particular color and make life easy for yourself. However, when writing professional code, it's important to give the user as much choice as possible, and this means giving the user a screen to select the color of their choice for shading.

This may sound complicated, but as discussed in Chapter 9, there are built-in dialogs available, courtesy of Microsoft. One of these dialogs is Color Selection. Suddenly, the shading process becomes easy! You don't even need to design a UserForm to allow the user to select the color because it has all been done for you.

You do need a blank UserForm to hold the Common Dialog control, though. Insert a UserForm and then drag the Common Dialog control onto it. If you are uncertain which control this is in the toolbox, look at the tooltip text on each control. Drag the control anywhere onto the form. The form never has to actually appear ‚ it is merely a container for the control, as shown in Figure 32-1.

Figure 32-1: A Common Dialog control on a UserForm

Insert the following code into a module:

Sub shade() UserForm3.CommonDialog1.CancelError = True UserForm3.CommonDialog1.Flags = &H1& On Error GoTo errhandler UserForm3.CommonDialog1.Action = 3 For Each window In Windows For Each Worksheet In window.SelectedSheets addr = Worksheet.Name & "!" & Selection.Address For counter = 1 To Application.Selection.Rows.Count If counter Mod 2 = 1 Then Range(addr).Rows(counter).Interior.Color = UserForm3.CommonDialog1.Color End If Next counter Next worksheet Next window Exit Sub errhandler: Exit Sub End Sub

To display the Common Dialog, you must first set the CancelError property to True so that when the user clicks the Cancel button on the form, an error will be generated and the Cancel action can be processed properly.

Set the Flags property to H1 (hexadecimal for 1) and point the On Error routine to an error-handling routine. If the Flags property is not set to 1, the Color Selection dialog will not appear. In practice, all the error-handling routine means is that when the user clicks the Cancel button, an error will be generated and the error-handling routine will exit the subroutine.

Because you are dealing only with rows within the selection and not cells , the routine is less complicated than the previous examples. It cycles through each window in the Windows collection and then through each worksheet within the SelectedSheets object. A variable called addr is loaded with the worksheet name and the selection address. A For..Next loop works through all the rows within the selection by using the rows.count property. Each row number is tested to see if it is odd or even by using the Mod function. If there is a remainder of 1, then the row is odd numbered and needs coloring. The row is set to the color selected in the Common Dialog control using the interior.color property.

Make a selection of a range on the spreadsheet and then run the code. The result should look similar to Figure 32-2, which is based on selecting the range A1..D13.

Figure 32-2: The result of the alternate colored lines macro (horizontal lines)

With some small modification, you can also make the code color in alternate columns:

Sub shade1() UserForm3.CommonDialog1.CancelError = True UserForm3.CommonDialog1.Flags = &H1& On Error GoTo errhandler UserForm3.CommonDialog1.Action = 3 For Each window In Windows For Each Worksheet In window.SelectedSheets addr = Worksheet.Name & "!" & Selection.Address For counter = 1 To Application.Selection.Columns.Count If counter Mod 2 = 1 Then Range(addr).Columns(counter).Interior.Color = _ UserForm3.CommonDialog1.Color End If Next counter Next worksheet Next window Exit Sub

This code works pretty much the same way as the previous code. The only part that changes is the For..Next loop; it is now checking the columns using the column.count property and changing the color of the row, which now refers to the Columns collection. Run this code, and the results should look like Figure 32-3. This is based on a selected range of A1..C17.

Figure 32-3: The result of the alternate colored columns macro (vertical columns)