I am using this code below. It works great with one little flaw. Whenever I delete something from a cell it gives me the MsgBox "This mold can not run in this machine." I have included a blank cell in my "Data" range but am still getting the message. Any ideas on why and or how to stop it?
Code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wSht As Worksheet
Dim vResult
Set wSht = Worksheets("Standards")
If Not WorksheetFunction.CountIf(wSht.Range("Data"), Target) > 0 Then
MsgBox "This is not a valid Mold Name. Please try again"
Target.Select
Target.ClearContents
Exit Sub
Else
With wSht.Range("Data")
vResult = .Find(What:=Target, After:=.Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, Target.Column + 5)
End With
If vResult <> "Y" Then
MsgBox "This Mold can not run in this machine." & _
"Please refer to the Mold/Machine Match list."
Target.Select
Exit Sub
End If
End If
End Sub
Display More
I have attached a file below with a WARNING: you will get locked into a loop of MsgBox's when you delete an item or enter in one that does not exist in the table. You will have to hold down a while on the Esc key to end it. (bit annoying)