Check if Value Exists in Another Sheet

Hello, how do I change the below code so when I enter a value on column M via paste it will return a messagebox saying "invalid data"?
the below code works however it isn't limited to just column M. it happens to all cells

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)

Dim FindString As String
Dim Rng As Range
On Error Resume Next


FindString = Range("M2").Value


If Trim(FindString) <> "" Then
    With Sheets("IDs").Range("A:A") 'searches all of column A
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
           
        Else
            MsgBox "Invalid Data!" 'value not found
            Target.Clear
           
        End If
    End With
End If
   
End Sub

---

Please help thank you

Not tested

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rFind as Range
Dim FindString As String
Dim Rng As Range
On Error Resume Next

Set rFind = Target.Cells(1,1)

If rFind.Column <> 13 Then Exit Sub

FindString = rFind.Value


If Trim(FindString) <> "" Then
    With Sheets("IDs").Range("A:A") 'searches all of column A
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
           
        Else
            MsgBox "Invalid Data!" 'value not found
            rFind.Clear
           
        End If
    End With
End If
    End Sub

---

Try this way. Use ClearContents, Clear will remove everything, including formatting. I don't think you need the variable either.

Option Explicit

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Or Target.Column <> 13 Or IsEmpty(Target) Then Exit Sub
    On Error Resume Next
    If Application.WorksheetFunction.CountIf(Sheets("IDs").Range("A:A"), Trim(Target.Value)) > 0 Then
        MsgBox "Invalid Data!"    'value exists
        Target.ClearContents
        Exit Sub
    End If
    On Error GoTo 0
End Sub

---

Both worked great! :)
Just to share, this is what I came up with on my own:

Code:

---

Private Sub Workbook_Open()

Dim rng As Range


lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row 'gets number of last used cell
Set rng = Range("M2:M" & lastrow)


With rng.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=IF(ISERROR(VLOOKUP(M2,IDs!$A$2:$A$65536,1,FALSE)),FALSE,TRUE)"
        .ErrorMessage = "INVALID DATA!"
    End With
End Sub

---

May not be the best out there but works just the same :)
I used workbook_open() because my friend's file is in xls

You've moved the code to a different event.

If you are going to use Data Validation then why use VBA?

Quote:

---

Originally Posted by royUK

You've moved the code to a different event.

If you are going to use Data Validation then why use VBA?

---

The file is in xls and won't accept data validation.. Also my friend's client only wants data validation :D

xls files can have data validation, it wouldn't work with VBA if that was not the case.