VBA: Highlight Duplicates in a Range

October 13th, 2008 | Categories: Cells, Columns & Rows | Tags: , , ,
-->

The following subroutine will highlight all the duplicate values in range in yellow. It does not matter whether the values are text or numbers. It uses Excel’s COUNTIF function to count up the duplicates and then sets the colour to yellow:

Sub Highlight_Duplicates(Values As Range)
Dim Cell

For Each Cell In Values
    If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then
        Cell.Interior.ColorIndex = 6
    End If

Next Cell
End Sub

And we have invoked it using a the click event:

Private Sub CommandButton1_Click()
Highlight_Duplicates (Sheets("Sheet1").Range("C10:F14"))

End Sub

and then if we press the button we see all the duplicates:

To download the .XLS file with this tutorial, click here

Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
Leave a comment | Trackback
  1. Chiranjeevi H
    September 28th, 2010 at 09:44
    Reply | Quote | #1

    Hi, i want to create a macro to find out the duplicates… lets say i have an ID 215, so if the same ID repeated in the same column.. select the entirerow and move it to the second sheet… please help me on this ASAP

  2. howard
    December 6th, 2010 at 09:18
    Reply | Quote | #2

    Good Day! Your program is nice. But I want to highlight a specific duplicate… likewise, in your example, the number “122″ only… and/or maybe, there will be an input cell to know what to highlight…
    Can you please help me? :)

    I would reall appreciate it! THANKS! :)

  3. Jim Law
    July 15th, 2011 at 16:13
    Reply | Quote | #3

    This sub does not work as written. It bombs out during the loop execution. Please help. Thanks.

    Sub Highlight_Duplicates(Values As Range)
    Dim Cell

    For Each Cell In Values
    If WorksheetFunction.CountIf(Values, Cell.Value) > 1 Then
    Cell.Interior.ColorIndex = 6
    End If

    Next Cell
    End Sub