How to Highlight Duplicates using Conditional Formatting in Excel VBA?

In order to highlight the duplicate values in a range, you could use the Conditional formatting in Excel and in this post, let’s have a look at how to do it using Excel VBA.

How to Highlight Duplicates using Conditional Formatting in Excel VBA?

Following is a code snippet that demonstrates how you can find and highlight all the duplicate values in a range programmatically using Excel VBA.

Sub HighlightRow()

Dim CellData

For Each CellData In Sheets("Sheet1").Range("A1:A30")
    If WorksheetFunction.CountIf(Sheets("Sheet1").Range("A1:A30"), CellData.Value) > 1 Then
        Cell.Interior.ColorIndex = 6
    End If

Next CellData

End Sub
How to Highlight Duplicates using Conditional Formatting in Excel VBA?