You can use the following basic syntax in VBA to apply conditional formatting to duplicate values in a specific range:

Sub ConditionalFormatDuplicates()

Dim rg As Range
Dim uv As UniqueValues

'specify range to apply conditional formatting
Set rg = Range("A2:A11")

'clear any existing conditional formatting
rg.FormatConditions.Delete

'identify duplicate values in range A2:A11
Set uv = rg.FormatConditions.AddUniqueValues
uv.DupeUnique = xlDuplicate

'apply conditional formatting to duplicate values
uv.Interior.Color = vbBlue
uv.Font.Color = vbWhite
uv.Font.Bold = True

End Sub

This particular example applies conditional formatting to duplicate values in the range A2:A11 of the current sheet in Excel.

The following example shows how to use this syntax in practice.

Example: Use VBA to Apply Conditional Formatting to Duplicate Values

Suppose we have the following column of values in Excel:

Suppose we would like to apply the following conditional formatting to duplicate values in column A:

• Blue background
• Black text
• Bold text

We can create the following macro to do so:

Sub ConditionalFormatDuplicates()

Dim rg As Range
Dim uv As UniqueValues

'specify range to apply conditional formatting
Set rg = Range("A2:A11")

'clear any existing conditional formatting
rg.FormatConditions.Delete

'identify duplicate values in range A2:A11
Set uv = rg.FormatConditions.AddUniqueValues
uv.DupeUnique = xlDuplicate

'apply conditional formatting to duplicate values
uv.Interior.Color = vbBlue
uv.Font.Color = vbWhite
uv.Font.Bold = True

End Sub

When we run this macro, we receive the following output:

Notice that conditional formatting is applied to each cell in column A with a duplicate value.

If you would like to apply conditional formatting to a different range of cells, simply change A2:A11 in the macro to a different range.

Also, if you’d like to remove all conditional formatting from cells in the current sheet, you can create the following macro to do so:

Sub RemoveConditionalFormatting()
ActiveSheet.Cells.FormatConditions.Delete
End Sub

Notice that all conditional formatting has been removed from the cells.