Chapter 33: Coloring Cells Containing Formulas

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