Skip to content

Excel VBA – Format Duplicates or Uniques with VBA Macro

    To highlight unique or duplicate values in a range use the AddUniqueValues method of the FormatConditions object. You can then specify using the DupeUnique property whether you want to format xlDuplicate or xlUnique.

    Sub formatduplicates()
Dim rg As Range
Set rg = Range("L5", Range("L5").End(xlDown))
Dim uv As UniqueValues
Set uv = rg.FormatConditions.AddUniqueValues
uv.DupeUnique = xlDuplicate
uv.Interior.Color = vbRed
End Sub

    The code above would apply the conditional formatting shown in the image below.

    Why this type of conditional formatting is potentially unsatisfactory: (1) If you format duplicates it formats the first instance as well as duplicates. (2) If you format uniques it actually only formats values that are not duplicated – therefore not all the uniques.

    Using a Formula to Format Duplicates

    A better way might be to use a COUNTIF formula to determine whether there is a reoccurence of a value. =COUNTIF(L$5:L5,L5)>1. You will need to state xlExpression as your ‘Type’ of condition.

    Sub formatduplicates2()
Dim rg As Range
Dim cf As FormatCondition
Set rg = Range("L5", Range("L5").End(xlDown))
Set cf = rg.FormatConditions.Add(Type:=xlExpression, Formula1:="=COUNTIF(L$5:L5,L5)>1")
cf.Interior.Color = vbRed
End Sub

    To format each unique value use =COUNTIF(L$5:L5,L5)=1

    Sub formatuniques()
Dim rg As Range
Dim cf As FormatCondition
Set rg = Range("L5", Range("L5").End(xlDown))
Set cf = rg.FormatConditions.Add(Type:=xlExpression, Formula1:="=COUNTIF(L$5:L5,L5)=1")
cf.Interior.Color = vbRed
End Sub

    The code above relates to the data shown below.