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