See attached file where I added this macro on second sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim lastRow As Long, r As Long
Dim myLot As String, myLot1 As String
Dim found As Range, found1 As Range
Set sh1 = ThisWorkbook.Sheets("production")
Set sh2 = ThisWorkbook.Sheets("production sheet")
If Target.Cells.Count = 1 And Target.Column = 7 Then
lastRow = sh2.Cells(Rows.Count, "b").End(xlUp).Row
For r = 5 To lastRow
If Trim(sh2.Cells(r, "g")) <> "" Then
myLot = sh2.Cells(r, "b")
myLot1 = ""
If Len(myLot) > 6 Then
myLot1 = Left(myLot, 6)
End If
Set found = sh1.Range("k:k").find(myLot, LookIn:=xlValues)
If Not found Is Nothing Then
If LCase(sh1.Cells(found.Row, "l")) Like "*not*dyed*" Then
sh1.Cells(found.Row, "l") = ""
End If
ElseIf myLot1 <> myLot Then
Set found = sh1.Range("k:k").find(myLot1, LookIn:=xlValues)
If Not found Is Nothing Then
sh1.Cells(found.Row, "l") = "PART DYED"
End If
End If
End If
Next r
End If
Set sh1 = Nothing
Set sh2 = Nothing
End Sub
Regards,
Antonio
Bookmarks