excel-vbaConditional formatting using VBA

Remarks

You cannot define more than three conditional formats for a range. Use the Modify method to modify an existing conditional format, or use the Delete method to delete an existing format before adding a new one.

FormatConditions.Add

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

Remove conditional format

Remove all conditional format in range:

Range("A1:A10").FormatConditions.Delete

Remove all conditional format in worksheet:

Cells.FormatConditions.Delete

FormatConditions.AddUniqueValues

Highlighting Duplicate Values

With Range("E1:E100").FormatConditions.AddUniqueValues
   .DupeUnique = xlDuplicate
   With .Font
       .Bold = True
       .ColorIndex = 3
   End With
End With

Highlighting Unique Values

With Range("E1:E100").FormatConditions.AddUniqueValues
   With .Font
       .Bold = True
       .ColorIndex = 3
   End With
End With

FormatConditions.AddTop10

Highlighting Top 5 Values

With Range("E1:E100").FormatConditions.AddTop10
    .TopBottom = xlTop10Top
    .Rank = 5
    .Percent = False
    With .Font
        .Bold = True
        .ColorIndex = 3
    End With
End With

FormatConditions.AddAboveAverage

With Range("E1:E100").FormatConditions.AddAboveAverage
    .AboveBelow = xlAboveAverage
    With .Font
        .Bold = True
        .ColorIndex = 3
    End With
End With

Operators:

NameDescription
XlAboveAverageAbove average
XlAboveStdDevAbove standard deviation
XlBelowAverageBelow average
XlBelowStdDevBelow standard deviation
XlEqualAboveAverageEqual above average
XlEqualBelowAverageEqual below average

FormatConditions.AddIconSetCondition

enter image description here

Range("a1:a10").FormatConditions.AddIconSetCondition
With Selection.FormatConditions(1)
    .ReverseOrder = False
    .ShowIconOnly = False
    .IconSet = ActiveWorkbook.IconSets(xl3Arrows)
End With

With Selection.FormatConditions(1).IconCriteria(2)
    .Type = xlConditionValuePercent
    .Value = 33
    .Operator = 7
End With

With Selection.FormatConditions(1).IconCriteria(3)
    .Type = xlConditionValuePercent
    .Value = 67
    .Operator = 7
End With

IconSet:

Name
xl3Arrows
xl3ArrowsGray
xl3Flags
xl3Signs
xl3Stars
xl3Symbols
xl3Symbols2
xl3TrafficLights1
xl3TrafficLights2
xl3Triangles
xl4Arrows
xl4ArrowsGray
xl4CRV
xl4RedToBlack
xl4TrafficLights
xl5Arrows
xl5ArrowsGray
xl5Boxes
xl5CRV
xl5Quarters

enter image description here

Type:

Name
xlConditionValuePercent
xlConditionValueNumber
xlConditionValuePercentile
xlConditionValueFormula

Operator:

NameValue
xlGreater5
xlGreaterEqual7

Value:

Returns or sets the threshold value for an icon in a conditional format.