In the following code, when I change Cell B7 the code runs up till the clear command (Line 7). Then it just stops after executing the clear command and if I change B7 again it doesn't trigger Worksheet_Change anymore.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo lastline
If Not Intersect(Target, Target.Worksheet.Range("B7")) Is Nothing Then
Dim i As Integer
Dim j As Integer
Target.Worksheet.Range("A10", "A300").Clear
j = 10
For i = 4 To 10
If ThisWorkbook.Sheets(1).Cells(i, 2) = Target Then
Application.EnableEvents = False
Target.Worksheet.Cells(j, 1) = ThisWorkbook.Sheets(1).Cells(i, 3)
Application.EnableEvents = True
j = j + 1
End If
Next i
End If
Application.EnableEvents = True
lastline:
Application.EnableEvents = True
End Sub
I have tried adding a MsgBox before Line 2 to see if it is a loop, but that message box only runs once.
Edit: In sheet 1 is a list of items along with their tag. There are duplicates in sheet 1, for example: Apple 1, Apple 3. What the code should do is when B7 in Sheet 3 is changed (sheet 3 is the target), It will find all the items in Sheet 1 with the same name as the new value of B7 and update the tag in Sheet 3.