VBA To Display Pop Up Alert When Duplicate Entry Is Made

[patel]

try this code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rFound As Excel.Range
    If Target.Column = 2 Then
      NewVal = Target.Value
      Application.EnableEvents = False
      Application.Undo 'the previous value is re-established.
      Set rFound = Range("B:B").Find(What:=NewVal, MatchCase:=False, Lookat:=xlWhole)
      If Not rFound Is Nothing Then
         If MsgBox("The registration Number  " & Target.Value & _
            "    has been found in row  " & rFound.Row & vbCrLf & vbCrLf & _
            "Do you want to view this entry?", vbQuestion + vbYesNo, "Confirm") = vbYes Then
                rFound.Activate
                Target.Value = NewVal
                Application.EnableEvents = True
         End If
      Else
        Target.Value = NewVal
        Application.EnableEvents = True
      End If
    End If
     
End Sub