For Loop in Worksheet change malfunction-CodePudding

Please I have an issue, everytime a change occcurs on the sheet it affects all the rows instead of the row (i) concerned. Confused. Don't for-loops work for worksheet_change ? Pls help. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LR As Long

'create a variable for last row of column C, LR
LR = Cells(Rows.Count, "C").End(xlUp).Row


For i = 2 To LR
      If Cells(i, 6) = "Yes" And Cells(i, 7).Value = "Full" Then 
       Target.Value = Cells(i, 3).Value
       Cells(i, 9).ClearContents
       Cells(i, 10).Value = Cells(i, 8).Value   Cells(i, 9).Value
     End If
    
    If Not Intersect(Target, Range("G" & i & ":G" & LR)) Is Nothing And Range("F" & i) = "Yes" 
    And Target.Value = "Full" Then
      Application.EnableEvents = False
      Cells(i, 8).Value = Cells(i, 3).Value
      Cells(i, 9).ClearContents
      Cells(i, 10).Value = Cells(i, 8).Value   Cells(i, 9).Value
      Application.EnableEvents = True
    End If
    
    If Not Intersect(Target, Range("G" & i & ":G" & LR)) Is Nothing And Range("F" & i) = "Yes" And 
    Target.Value = "Portion" Then
      Application.EnableEvents = False
      Cells(i, 8).Value = Cells(i, 3).Value
      Cells(i, 10).Value = Cells(i, 8).Value   Cells(i, 9).Value
      Application.EnableEvents = True
    End If
    
Next i
End Sub

CodePudding user response：

It seems you need to launch this event for the columns A-E. So, you can start your macro with:

IF Target.Column <= 5 THEN
...
END IF 'at the end of your macro

Like this, when you launch code like Cells(i, 8).Value = ..., Cells(i, 10).Value = ..., ... this macro will be called but it will be stopped immediately.

Apparently you are checking on column, maximum 10, which is in the range of the cells you are changing within your macro. Let's go for another approach:

At the very beginning of your macro, put this line:

Application.EnableEvents = False

At the very end of your macro, put this line:

Application.EnableEvents = True

(and remove the other occurences).

This will make sure you don't call your macro while running it.