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?
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
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)