Data Mining in Excel - Automated Data Analysis Using Excel - page 178

FIGURE 5.13 The Color Cell background sample application.
The Color Cell Background can be run from the menu by selecting A DA->Chapter 5 ->Color
Cell B ackground, which will activate the GUI shown in Figure 5.13. The only real difference
between this application and the Color Cell Font sample application is the properties that have to
be set in order to color the background interior of a Worksheets cell. When the “Highlight Cells”
button is pressed, the following code is executed.
Private Sub Button_Highlight_Click()
If RefEdit.Text = "" Then
MsgBox "You Must Choose a Range!", vbOKOnly + vbExclamation,
"No Range Selected!"
Exit Sub
End If
If ColorComboBox.Text = "" Then
MsgBox "You Must Choose a Highlighting Color", vbOKOnly +
vbExclamation, "Highlighting Color Not Specified!"
Exit Sub
End If
Range(RefEdit.Text).Interior.Color =
Colors(ColorComboBox.ListIndex + 1, 2)
Range(RefEdit.Text).Interior.Pattern = xlSolid
End Sub
As in the previous instance, a valid range and color must be selected, and checking mechanisms
are built into the code to ensure that they are present. To highlight the background of a cell, the
cell's interior color must be set along with the interior pattern set to solid. The two highlighted
areas of code just presented show how to accomplish this. It is worth mentioning that the same
effort could be achieved by means of the “with-end with” construct as follows.
Range(RefEdit.Text).Select
With Selection.Interior
.Color = Colors(ColorComboBox.ListIndex + 1, 2)
.Pattern = xlSolid
End With