Worksheet_Change as Data Validation

[m1ngle]

I am trying to use worksheet_change as a data validation check. I want to insure values pasted into cells range a1:a3000 match a list of values from a list. I'm afraid I'm lost and don't know how to proceed.
Here is what I have so far
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rang As Range
Set Rang = Worksheets("DATA INPUT SHEET").Range("A1:A3000")
If Intersect(Target, Rang) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub</code>What I want to add would be along the lines of:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">If Target.Value <> Worksheets("Worksheet 2".Range("B2:B7") Then
MsgBox "The value you entered is not valid"</code>Does anyone have any suggestions?

 

[sericom]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rang As Range
    Set Rang = Worksheets("DATA INPUT SHEET").Range("A1:A3000")
    If Not Intersect(Target, Rang) Is Nothing Then
        For Each c In Target
            Set fnd = Worksheets("Worksheet 2").Range("B2:B7").Find(c.Value, lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False)
            If fnd Is Nothing Then
                MsgBox "The value you entered at " & c.Address & " is not valid"
            End If
        Next
    End If
End Sub

 

[m1ngle]

Thanks so much. It was extremely innovative. Is there any way to not generate the error when the contents are deleted?

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rang As Range
    Set Rang = Worksheets("DATA INPUT SHEET").Range("A1:A3000")
    If Not Intersect(Target, Rang) Is Nothing Then
        For Each c In Target
            Set fnd = Worksheets("Worksheet 2").Range("B2:B7").Find(c.Value, lookat:=xlWhole, LookIn:=xlValues, MatchCase:=False)
            If fnd Is Nothing Then
                MsgBox "The value you entered at " & c.Address & " is not valid"
            End If
        Next
    End If
End Sub

 

[sericom]

If fnd Is Nothing And c <> "" Then

 

[m1ngle]

Thanks sericom! This did the trick.