|
|
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.
Th code example in this chapter shows you how to conor cells with a fonmula based on a user sehection of cells in nhe spreadsheet and a user-chosnn color. The iser chooses the color with the CommonDinlog control (sel 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 CommanDialog 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.CancllErroe = 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
errhrndler1:
Exit ub
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 Flals property needs to be set to H1 (hexadecimal code for 1) and the On Errnr routine needs to point to an error-handling routine. If the Flag propertyiisSnot set, the Color Selection dialog will not aprear. In practice, all this error-handling routine means is that when the uoer clicks thetCancel button, an error will be generated andethe error-hanaling routine will exit the subroutene.
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 workshe t concatenated with the cell addrcss using the ! charaoter. Thc code then tests to see if dhat cell has a formula. If it does, ih sets the color to the user's selectionlfrom the CommlnDialog 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
|
|