The Format Conditions Collection

Excel allows us to apply conditional formatting to a cell (or a range of cells). A conditional format is a format that is applied if and only if certain conditions are met by the contents of the cell. For instance, we may want to make a number red if it is negative, black if it is positive, or green if it is 0. This requires three conditional formats.

The FormatConditions property of a Range object returns a FormatConditions collection that can contain up to three FormatCondition objects, each of which represents a conditional format.

The Add method of the FormatConditions collection is used to add FormatCondition objects to the collection. However, attempting to add more than three such objects will generate an error. The syntax for the Add method is:

FormatConditionsObject.Add(Type, Operator, Formula1, Formula2)

The required Type parameter specifies whether the conditional format is based on the value in the cell or an expression. It can be either of the following XlFormatConditionType constants:

Enum XlFormatConditionType xlCellValue = 1 xlExpression = 2 End Enum

When Type is xlCellValue, the Operator parameter specifies the operator to use with that value. If Type is xlExpression, the Operator argument is ignored. The value of Operator is one of the following constants:

Enum XlFormatConditionOperator xlBetween = 1

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

A

B

C

2

2

3

100

3

-1

A

5

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

.FormatConditions.Add xlExpression, ,

Range("A1").Value = Range("A2").Value .FormatConditions(1).Interior.Color =

RGB(0, 0, 255) .FormatConditions.Add xlExpression, ,

Range("A1").Value <> Range("A2").Value .FormatConditions(2).Interior.Color = RGB(2 55, 0, 0) End With

As the previous examples show, the actual formatting is done by setting some of the properties of children of the FormatCondition object. In particular, the Borders, Font, and Interior properties return child objects of the same name, whose properties can be set to indicate the desired formatting.

Note finally that an existing FormatCondition object can be deleted using the Delete method of the FormatConditions collection, and it can be changed using the Modify method of the FormatCondition object. The Modify method has the syntax:

FormatConditionObject.Modify(Type, Operator, Formula1, Formula2) where the parameters are identical to those of the Add method.

+17 -16

Responses

  • lionella
    What is format condition in VBA?
    5 months ago

Post a comment