Table of Contents
To apply conditional formatting to duplicate values, select the cells you want to apply it to and then click the Home tab, select Conditional Formatting and then select Highlight Cells Rules, and then select Duplicate Values. This will open the Duplicate Values dialog box, where you can customize the formatting to your desired specifications. Finally, click OK to apply the formatting.
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.