B
burl_h
I'm using the following code to stop duplicate records being entered
into column A. In principle the macro works great but I would like to
add some enhancements.
First, on entry I'd like a message to say which cell has a duplicate
record, if one exists.
Secondly, I'd like the cell pointer (active cell) to goto the
duplicate record if one exists.
Any help would be greatly appreciated.
Thanks
burl_h
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Integer
If Target.Column = 1 Then
If Not IsEmpty(Target.Value) Then
LastRow = Cells(65536, Target.Column).End(xlUp).Row
For i = 1 To LastRow
If i <> Target.Row Then
If Cells(i, Target.Column).Value = Target.Value Then
MsgBox Target.Value & " already exists.", vbExclamation
Target.Value = Empty
Exit For
End If
End If
Next i
End If
End If
End Sub
into column A. In principle the macro works great but I would like to
add some enhancements.
First, on entry I'd like a message to say which cell has a duplicate
record, if one exists.
Secondly, I'd like the cell pointer (active cell) to goto the
duplicate record if one exists.
Any help would be greatly appreciated.
Thanks
burl_h
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Integer
If Target.Column = 1 Then
If Not IsEmpty(Target.Value) Then
LastRow = Cells(65536, Target.Column).End(xlUp).Row
For i = 1 To LastRow
If i <> Target.Row Then
If Cells(i, Target.Column).Value = Target.Value Then
MsgBox Target.Value & " already exists.", vbExclamation
Target.Value = Empty
Exit For
End If
End If
Next i
End If
End If
End Sub