Highlight Duplicates in Excel

Highlight Duplicates in Excel

Open Excel in VBA mode and then copy and Past  following code under the WorkSheet.Then Run the code then you can get the result with in one second.This will save your time when you work with large amount of data.

Coding

Option Explicit
----------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Row = 1 Then Exit Sub             ' IF ITS A HEADER, DO NOTHING.
   
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
   
    Dim myDataRng As Range
    Dim cell As Range
   
    ' WE WILL SET THE RANGE (SECOND COLUMN).
    Set myDataRng = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)
   
    For Each cell In myDataRng
        cell.Offset(0, 0).Font.Color = vbBlack          ' DEFAULT COLOR.
   
        ' LOCATE DUPLICATE VALUE(S) IN THE SPECIFIED RANGE OF DATA.
        If Application.Evaluate("COUNTIF(" & myDataRng.Address & "," & cell.Address & ")") > 1 Then
            cell.Offset(0, 0).Font.Color = vbRed        ' CHANGE FORE COLOR TO RED.
        End If
    Next cell
   
    Set myDataRng = Nothing
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub


Screen Shot 01

Screen Shot 02

Download Sample File Here