Forward not Backwards

[kev6264]

I have this procedure that works great, but if I select a two-letter code in a cell that precedes another cell, the procedure allows the duplicate. How can I turn this into a forward and backwards checker?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 4 Then Exit Sub
    If Application.WorksheetFunction.CountIf(Range("$D$2:$D" & Target.Row), Target) > 1 Then
        MsgBox "You have chosen a duplicate sine. Please try again"
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub

 

[erik.van.geit]

only two-letter-combinations ?

allowing alpha-numerics you have only 36x36=1296 combinations while you're already at row 2512
did you try filling in more characters ?

try out your code on a new sheet

 

[kev6264]

Dumb question... This code should be inserted into the SHEET not a module right?

 

[kev6264]

The light just came on... I have the cells validated against a list.. Guess where the list is copied? Column 4 down below my entry sheet. Boy, do I feel really dumb now . Erik, thanks for sticking with me this long. Works great now that I moved my validation list. DAAAH

 

[erik.van.geit]

glad to shed some light in the dark
you're most WELCOME !!!