excel-vba Conditional formatting using VBA FormatConditions.Add

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

Syntax:

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

Parameters:

NameRequired / OptionalData Type
TypeRequiredXlFormatConditionType
OperatorOptionalVariant
Formula1OptionalVariant
Formula2OptionalVariant

XlFormatConditionType enumaration:

NameDescription
xlAboveAverageConditionAbove average condition
xlBlanksConditionBlanks condition
xlCellValueCell value
xlColorScaleColor scale
xlDatabarDatabar
xlErrorsConditionErrors condition
xlExpressionExpression
XlIconSetIcon set
xlNoBlanksConditionNo blanks condition
xlNoErrorsConditionNo errors condition
xlTextStringText string
xlTimePeriodTime period
xlTop10Top 10 values
xlUniqueValuesUnique 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:

NameDescription
xlBeginsWithBegins with a specified value.
xlContainsContains a specified value.
xlDoesNotContainDoes not contain the specified value.
xlEndsWithEndswith 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


Got any excel-vba Question?