Check for duplicates in column

In this article, we have created a macro to highlight duplicate values.

Raw data consists of different car names. We want to highlight duplicate entries.

Press the “Highlight duplicate” button to run the macro and highlight duplicate entries.

Logic explanation

We have created “Check_Dups” macro to highlight duplicate entries. They will be highlighted in red color. In the macro, we check for count of a particular entry. If it is greater than 1, then the data will be highlighted.

Please follow below for the code

Option Explicit

Sub Check_Dups()

'Declaring variables
Dim Cell As Variant
Dim Source As Range

'Initializing source range
Set Source = Range("A9:A24")

'Removing any previous formatting from the source
Source.Interior.Color = RGB(221, 235, 247)

'Looping through each cell in the source range
For Each Cell In Source
    
    'Checking whether value in cell already exist in the source range
    If Application.WorksheetFunction.CountIf(Source, Cell) > 1 Then
        
        'Highlighting duplicate values in red color
        Cell.Interior.Color = RGB(255, 0, 0)
        
    End If
Next

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com