Example
Syntax:
FormatConditions.Add(Type, Operator, Formula1, Formula2)
Parameters:
| Name | Required / Optional | Data Type |
|---|
| Type | Required | XlFormatConditionType |
| Operator | Optional | Variant |
| Formula1 | Optional | Variant |
| Formula2 | Optional | Variant |
XlFormatConditionType enumaration:
| Name | Description |
|---|
| xlAboveAverageCondition | Above average condition |
| xlBlanksCondition | Blanks condition |
| xlCellValue | Cell value |
| xlColorScale | Color scale |
| xlDatabar | Databar |
| xlErrorsCondition | Errors condition |
| xlExpression | Expression |
| XlIconSet | Icon set |
| xlNoBlanksCondition | No blanks condition |
| xlNoErrorsCondition | No errors condition |
| xlTextString | Text string |
| xlTimePeriod | Time period |
| xlTop10 | Top 10 values |
| xlUniqueValues | Unique values |
Formatting by cell value:
With Range("A1").FormatConditions.Add(xlCellValue, xlGreater, "=100")
With .Font
.Bold = True
.ColorIndex = 3
End With
End With
Operators:
| Name |
|---|
| xlBetween |
| xlEqual |
| xlGreater |
| xlGreaterEqual |
| xlLess |
| xlLessEqual |
| xlNotBetween |
| xlNotEqual |
If Type is xlExpression, the Operator argument is ignored.
Formatting by text contains:
With Range("a1:a10").FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="egg")
With .Font
.Bold = True
.ColorIndex = 3
End With
End With
Operators:
| Name | Description |
|---|
| xlBeginsWith | Begins with a specified value. |
| xlContains | Contains a specified value. |
| xlDoesNotContain | Does not contain the specified value. |
| xlEndsWith | Endswith the specified value |
Formatting by time period
With Range("a1:a10").FormatConditions.Add(xlTimePeriod, DateOperator:=xlToday)
With .Font
.Bold = True
.ColorIndex = 3
End With
End With
Operators:
| Name |
|---|
| xlYesterday |
| xlTomorrow |
| xlLast7Days |
| xlLastWeek |
| xlThisWeek |
| xlNextWeek |
| xlLastMonth |
| xlThisMonth |
| xlNextMonth |
