Check for existing numbers in colom with VBA

[dotchieJack]

Hello,
I have a colom (D) with empty spaces , I would like to have a message box if in the colom the value already exist but also if the value +1 exist when i fill in an number in one of the empty spaces
for example 1234 exists in colom D, then I should a messagebox like :Number 1234 and 1235 already exist
file included
thanks in advance

 

[Luke M]

To view code, right-click on sheet tab, view code. Triggers when user types something into col D.

 

[dotchieJack]

Hello Luke
Working great, thank you, and in such a short time, this is great
Have a nice evening
(problem solved)

 

[Chihiro]

Slightly different method.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim x, x2
lRow = Cells(Rows.Count, 4).End(xlUp).Row
If Target.Count > 1 Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub

If Not Intersect(Target, Range("D:D")) Is Nothing Then
    With Range("D2:D" & lRow - 1)
        x = Evaluate("MATCH(" & Target.Address & "," & .Address & ",0)")
        x2 = Evaluate("MATCH(" & Target.Value + 1 & "," & .Address & ",0)")
        If IsNumeric(x) Or IsNumeric(x2) Then
            MsgBox "Code " & Target.Value & " or " & Target.Value + 1 & " already exists"
            Target.Select
            Target.ClearContents
        End If
    End With
End If
End Sub

 

[dotchieJack]

Hello Chihiro,
Thank you for your quick response and great solution. I like the

Target.ClearContents

part.
Have a nice evening