A simple Sub procedure: Color banding

Suppose that you want to apply alternate color banding to a selected range of cells. Execute the following steps:

  1. Start a blank workbook.
  2. Press Alt+F11 to activate VBE.
  3. Add a new standard VBA module to the project of the workbook by executing the following steps:
    • In the Project Explorer window, right-click the project’s name (by default, it is named VBAProject).
    • Choose Insert | Module from the shortcut menu.
      • By default, it is named Module1. You can change its name in the Properties window. If the Properties window is not visible, press F4.
  4. In the Project Explorer window, double-click the newly added module’s name to activate its code window.
  5. Enter the following Sub procedure in the code window:
Sub CreateAlternateColorBanding()
'To alternately color rows within a selection
   Dim rw As Range
   For Each rw In Selection.Rows
      If rw.Row Mod 2 <> 0 Then _
         rw.Interior.ColorIndex = 15
   Next rw
End Sub

Note: An underscore character (preceded by a space) is to separate a single long code statement that does not fix on a single line. Separating a single long statement into a few lines also makes the code easier to read, without a need to scroll horizontally in the code window.

 

Exercise 2-1: Executing a Sub procedure by using the Macro dialog box

After storing the CreateAlternateColorBanding procedure in a standard VBA module, execute the following steps to run the sample procedure:

  1. Press Alt+F11 to activate Excel.
  2. Select a range of cells of at least 2 rows by 2 columns.
  3. Press Alt+F8 to display the Macro dialog box.
  4. Select the macro named CreateAlternateColorBanding, and click Run.
    • Note: If CreateAlternateColorBanding is not shown in the Macro dialog box, choose the name of the workbook that stores the VBA code for that procedure from the drop-down control labelled Macros in.

 

Exercise 2-2: Executing a Sub procedure by using a shortcut key combination

A faster way to run a Sub procedure is to assign it a shortcut key combination or to create a custom control on the Ribbon. I discuss the latter in my future book for intermediate users. The former can be easily done by executing the following steps:

  1. In Excel, press Alt+F8 to display the Macro dialog box.
  2. Select the macro CreateAlternateColorBanding, and click the Options button to display the Macro Options dialog box.
  3. Assign your shortcut key combination by pressing Shift+B, for example, and click OK to close the Macro Options dialog box.
    • The shortcut key combination is then Ctrl+Shift+B.
  4. Click Cancel to close the Macro dialog box.
  5. Select a range of cells and test the procedure again by pressing the newly assigned shortcut key combination.

 

Exercise 2-3: Making a Sub procedure available to all opened workbooks

If to make a Sub procedure available for other workbooks and not just for the workbook that stores the VBA code for that procedure, you may store the code in your Personal Macro Workbook. This workbook is named Personal.xlsb, as you can see in the Project Explorer window.

If Personal.xlsb does not exist, execute the following steps to create the workbook.

  1. In Excel, choose Developer | Code | Record Macro to display the Record Macro dialog box.
  2. From the drop-down control labelled Store macro in, choose Personal Macro Workbook and click OK to accept other default settings.
    • By clicking OK, the Personal Macro Workbook is created in VBE.
  3. Choose Developer | Code | Stop Recording or alternatively click the square icon on the status bar to stop recording a dummy macro in the newly created Personal Macro Workbook.
  4. Press Alt+F11 to activate VBE.
  5. In the code window, delete the dummy macro, but retain its VBA module in the Project Explorer window for later use.

To make CreateAlternateColorBanding available for all opened workbooks, copy the Sub procedure and paste it into the code window of the VBA module in the Personal Macro Workbook. You can also assign it a shortcut key combination, as discussed in Exercise 2-2 above.