If statement locks up excel

[velocity1234]

I have the following code. It copies values from one cell to another given a simple criteria when there is a "Change" on the sheet.

The code works, but then it hangs up as if I am overloading the program. Is there an easier way
to code this so it does not hang.

Private Sub Worksheet_Change(ByVal Target As Range)


If Range("LastSPY").Value <= Range("LowestLowSPY").Value Then
Range("LowestLowSPY").Value = Range("LastSPY").Value
End If


If Range("LastSPY").Value >= Range("HighestHighSPY").Value Then
Range("HighestHighSPY").Value = Range("LastSPY").Value
End If


If Range("LastVXX").Value <= Range("LowestLowVXX").Value Then
Range("LowestLowVXX").Value = Range("LastVXX").Value
End If


If Range("LastVXX").Value >= Range("HighestHighVXX").Value Then
Range("HighestHighVXX").Value = Range("LastVXX").Value
End If


End Sub

 

[mikerickson]

It looks like you might be getting cascading events. The Change code changes the value of a cell which calls the Change event, which changes the value of a cell, which calls the Change event, which ....

You can disable the calling of events with

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'...

Application.EnableEvents = True
End Sub

Be sure to code your routine so that all possibilities include the EnableEvents = True line before exiting the Sub (no Exit Sub).

 

[velocity1234]

Thank you so much. It worked like a charm!!!

That was a huge problem for me.