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
According to MS, the UniqueValues property is used to set OR GET whether the rule is looking for unique or duplicate properties. Your example just creates a new rule and sets the value.
I’m looking to retrieve an existing rule and get the value, and I just cannot find any examples of how to do it – VBA justs crashes on me with “property not supported”, so clearly I don’t know how to extract the UniqueValues object from the FormatRule object.
Hi Anthony…You’re right that `UniqueValues` is a property of the `FormatCondition` object in VBA when dealing with conditional formatting. However, retrieving an existing rule and getting its `UniqueValues` property is tricky because VBA’s `FormatCondition` objects are not always well-documented for reading properties beyond basic conditions.
Here’s how you can **retrieve the existing conditional formatting rule** and check if it is a unique/duplicate rule:
### Solution:
“`vba
Sub CheckUniqueValuesCondition()
Dim ws As Worksheet
Dim rng As Range
Dim fc As FormatCondition
‘ Set your worksheet and the range where the conditional formatting is applied
Set ws = ActiveSheet
Set rng = ws.Range(“A1:A100”) ‘ Adjust as needed
‘ Loop through the format conditions
For Each fc In rng.FormatConditions
‘ Check if the condition is a UniqueValues condition
If TypeName(fc) = “UniqueValues” Then
Debug.Print “UniqueValues condition found.”
Debug.Print “IsUnique: ” & fc.UniqueValues
Else
Debug.Print “Different condition found: ” & TypeName(fc)
End If
Next fc
End Sub
“`
### Explanation:
1. The macro loops through the `FormatConditions` collection of the specified range.
2. It checks if the condition is of type `”UniqueValues”`.
3. If it is, it prints whether the rule is for **unique** or **duplicate** values (`True` for unique values, `False` for duplicates).
### Possible Issues:
– If there are **multiple** rules, it will only display those that are `UniqueValues` conditions.
– If VBA crashes with **”Property not supported”**, ensure the conditional formatting rule is indeed a **UniqueValues** rule and not something else like a color scale, data bar, or formula-based rule.