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
When we run this macro, we receive the following output:
Notice that all conditional formatting has been removed from the cells.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Unique Values in Range
VBA: How to Count Cells with Specific Text
VBA: How to Write COUNTIF and COUNTIFS Functions
