Microsoft Office Tutorials and References
In Depth Information
Figure 19-24. A conditionally formatted range
xlNotBetween = 2
xlEqual = 3
xlNotEqual = 4
xlGreater = 5
xlLess = 6
xlGreaterEqual = 7
xlLessEqual = 8
End Enum
If Type is xlCellValue , then Formula1 and Formula2 give the comparison values used with
Operator and the cell value. Note that Formula2 is used only with the xlBetween and
xlNotBetween constants.
For example, the following code sets the interior color of a cell in the range A1:C4 to 25%
grayscale if the number is between 0 and 10 (inclusive) and to white otherwise. The results are
shown in Figure 19-24 . Note that we first cleared all conditional formatting before creating new
FormatCondition objects. Note also that an empty cell is treated as if it contains a 0.
Dim rng As Range
Dim i As Integer
Set rng = Range("A1:C4")
' Clear all existing formats
For i = rng.FormatConditions.Count To 1 Step -1
rng.FormatConditions(i).Delete
Next
With rng
.FormatConditions.Add xlCellValue, xlBetween, 0, 10
.FormatConditions(1).Interior.Color = RGB(196, 196, 196)
.FormatConditions.Add xlCellValue, xlNotBetween, 0, 10
.FormatConditions(2).Interior.Color = RGB(255, 255, 255)
End With
Figure 19-24. A conditionally formatted range
When Type is xlExpression , Formula2 is ignored, and Formula1 gives the formula or
expression that determines the condition. This parameter can be a constant, a string, a cell
reference, or a formula. To illustrate, the following code sets the interior color based on whether
cells A1 and A2 contain the same value:
Dim rng As Range
Dim i As Integer
Set rng = Range("A1:A2")
' Clear all existing formats
For i = rng.FormatConditions.Count To 1 Step -1
rng.FormatConditions(i).Delete
Next
With rng
 
Search JabSto ::




Custom Search