Databases Reference
In-Depth Information
'Add Formula Here (Conditions under which to apply formatting)
If DeleteExisting = True Then Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:=formula$
'If Specified Add Background Color Here
If IsMissing(backcolor) = False Then
Selection.FormatConditions(ConditionNumber).Interior.Color
Index = backcolor
End If
'Set Font Attributes Here
With Selection.FormatConditions(ConditionNumber).Font
.Bold = fontBold
.Italic = fontItal
.ColorIndex = fontColor
End With
'For Cell Outlining
With Selection.FormatConditions(ConditionNumber).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
Conditional Formatting does have some limitations, however, and the biggest limitation is that
functions that reside in other Worksheets or Workbooks cannot be utilized as conditional formatting
criteria, as shown in Figure 4.16. A sample application has been created to demonstrate the use
and limitations of conditional formatting using VBA macro structures. The sample can be run by
selecting ADA->Chapter 4->Conditional Formatting from the Excel menu. This will bring up the
GUI shown in Figure 4.17.
Pushing the first button titled “Load Example Sheet” will automatically load a template file
required for the example. The code and mechanics of how this is accomplished is discussed in detail
in Chapter 5. The second button titled “Highlight Default” applies conditional formatting, which
FIGURE 4.16
Conditional formatting limited to native functions.
