VBA Conditional Formatting syntax for xlUniqueValues

I am trying to highlight duplicates using conditional formatting under VBA.

If you look at Conditional formatting using VBA, one of the XlFormatConditionType (Type) for FormatConditions.Add is xlUniqueValues.

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

The listed operators are: xlBetween; xlEqual; xlGreater; xlGreaterEqual; xlLess; xlLessEqual; xlNotBetween; xlNotEqual.

None of these apply to xlUniqueValues. I have tried them all, including xlDuplicate and xlUnique, which do not throw errors. Types xlTextString uses TextOperator, and xlTimePeriod uses DateOperator as operator modifiers, but there is no info on a modifier for xlUniqueValues.

With Range("a1:a10").FormatConditions.Add(xlTextString, TextOperator:=xlContains, String:="egg") .Interior.Color = RGB(255, 166, 0)
End With

My code:

With Range("B6:B30").FormatConditions.Add(xlUniqueValues, xlDuplicate) .Interior.Color = RGB(255, 166, 0)
End With

This highlights all unique values, no matter what I put in for the operator. The syntax is correct, but I can find no info on how to highlight duplicates.

What is the correct syntax to highlight duplicates?

I have this working as a macro captured conditional formatting (shown below), but I'd like to get it working with this syntax, since it is cleaner.

Range("B6:B30").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = RGB(255, 166, 0) .TintAndShade = 0
End With

1 Answer

It might be cleaner but it does not work as you have already discovered. The FormatCondition.Add's Operator parameter in this case is ignored:

Can be one of the following XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual. If Type is xlExpression, the Operator argument is ignored.

The UniqueValues documentation states that:

The UniqueValues object uses the DupeUnique property to return or set an enum that determines whether the rule should look for duplicate or unique values in the range.

Meaning that if you want to check for duplicates, use DupeUnique property which:

Returns or sets one of the constants of the XlDupeUnique enumeration, which specifies if the conditional formatting rule is looking for unique or duplicate values.

Your code will change to:

With Range("B6:B30").FormatConditions.Add(xlUniqueValues) .DupeUnique = xlDuplicate .Interior.Color = RGB(255, 166, 0)
End With

OR

With Range("B6:B30").FormatConditions.AddUniqueValues .DupeUnique = xlDuplicate .Interior.Color = RGB(255, 166, 0)
End With