Excel VBA Macro Programming

Overview

On complex spreadsheets, it is not always easy to see which cells have actual numbers and which cells are formulas. You can use different displays to fix this by switching to a view showing cell formulas within the spreadsheet, but it does display with wider columns , and you cannot see the original result, only the formula.

The code example in this chapter shows you how to color cells with a formula based on a user selection of cells in the spreadsheet and a user - chosen color. The user chooses the color with the CommonDialog control (see Chapter 10 for how to use this), which has a built-in color chart dialog to choose from. If you have been following these examples, you already have a CommonDialog control set up on a UserForm.

If you do not yet have a UserForm, insert one and drag the CommonDialog 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 actually has to appear ‚ it is merely a container for the control.

Insert the following code into a module:

Sub col_cell() UserForm3.CommonDialog1.CancelError = True UserForm3.CommonDialog1.Flags = &H1& On Error GoTo errhandler1 UserForm3.CommonDialog1.Action = 3 For Each window In Windows For Each Worksheet In window.SelectedSheets For Each cell In Application.Selection addr = Worksheet.Name & "!" & cell.Address If Range(addr).HasFormula Then Range(addr).Interior.Color =_ UserForm3.CommonDialog1.Color Next cell Next worksheet Next window Exit Sub errhandler1: 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 will be processed .

The Flags property needs to be set to H1 (hexadecimal code for 1) and the On Error routine needs to point to an error-handling routine. If the Flag property is not set, the Color Selection dialog will not appear. In practice, all this 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.

Once the user selects a color from the dialog, the code cycles through the windows in the Windows collection and through the worksheets within the selected sheet's collection for each worksheet. Each cell within the selection is then gone through. The variable addr holds the name of the worksheet concatenated with the cell address using the ! character. The code then tests to see if that cell has a formula. If it does, it sets the color to the user's selection from the CommonDialog form using the Interior.Color property.

Make a selection over cells that have a combination of formulas and numbers, and then run the code. You should have results similar to Figure 33-1.

Figure 33-1: Example of coloring cells containing formulas