Unwanted 'loop' in Worksheet change?

[WatfordKev]

the following works perfectly for me

Private Sub Worksheet_Change(ByVal Target As Range)

' dynamic update of status :: Q1

If Range("D80").Value >= Range("C69") Then
Range("E80").Interior.ColorIndex = 4
Range("E80").Value = "Green "
ElseIf Range("D80").Value > Range("D69") And Range("D80").Value < Range("C69") Then
Range("E80").Interior.ColorIndex = 6
Range("E80").Value = "Amber "
If Range("D80").Value >= Range("B69") And Range("E80").Value = "Amber " Then
Range("E80").Interior.ColorIndex = 4
Range("E80").Value = "Green "
End If
ElseIf Range("D80").Value <= Range("D69") Then
Range("E80").Interior.ColorIndex = 3
Range("E80").Value = "Red "
End If

End Sub

However, the following does not - it appears to loop round and round and round!! All I have done (!) is add the lines in green, which does the same operation on the next rows on the worksheet! All I want is for the test to be run on the respective rows each time the worksheet gets changed!

Private Sub Worksheet_Change(ByVal Target As Range)

' dynamic update of status :: Q1

If Range("D80").Value >= Range("C69") Then
Range("E80").Interior.ColorIndex = 4
Range("E80").Value = "Green "
ElseIf Range("D80").Value > Range("D69") And Range("D80").Value < Range("C69") Then
Range("E80").Interior.ColorIndex = 6
Range("E80").Value = "Amber "
If Range("D80").Value >= Range("B69") And Range("E80").Value = "Amber " Then
Range("E80").Interior.ColorIndex = 4
Range("E80").Value = "Green "
End If
ElseIf Range("D80").Value <= Range("D69") Then
Range("E80").Interior.ColorIndex = 3
Range("E80").Value = "Red "
End If

' dynamic update of status :: Q2

If Range("D81").Value >= Range("C70") Then
Range("E81").Interior.ColorIndex = 4
Range("E81").Value = "Green "
ElseIf Range("D81").Value > Range("D70") And Range("D81").Value < Range("C70") Then
Range("E81").Interior.ColorIndex = 6
Range("E81").Value = "Amber "
If Range("D81").Value >= Range("B70") And Range("E81").Value = "Amber " Then
Range("E81").Interior.ColorIndex = 4
Range("E81").Value = "Green "
End If
ElseIf Range("D81").Value <= Range("D70") Then
Range("E81").Interior.ColorIndex = 3
Range("E81").Value = "Red "
End If

End Sub

 

[Andrew Poulsom]

Put this at the beginning of your procedure:

Application.EnableEvents = False

and set it back to True at the end.

 

[WatfordKev]

Thank you Andrew! I wonder if I ever could have figured that out for myself!