temporarily disable the events...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
application.enableevents = false
With ActiveSheet
For Each c In Worksheets("Goals").Range("C3:C54").Cells
If c.Value = .Range("B4") And c.Offset(0, -1).Value = .Range("B3")
Then
.Range("I36") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
application.enableevents = true
End If

--
HTH...

Jim Thomlinson


"Ayo" wrote:

I am trying to write a code that does the following:

If the value in Range(B3) or Range(B4) is changed perform some calculations
in the worksheet.

But everytime I try to run the code, when it gets to:
.Range("I36") = c.Offset(0, 1).Value
it jumps back to the beginning again which I assume is because the value in
that cell is changed and the Sub is running itself again which is going to
end up being as infinite loop. How can I write a code that does what I want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address() = "$B$3" Then
With ActiveSheet
For Each c In Worksheets("Goals").Range("C3:C54").Cells
If c.Value = .Range("B4") And c.Offset(0, -1).Value = .Range("B3")
Then
.Range("I36") = c.Offset(0, 1).Value
.Range("I37") = c.Offset(0, 3).Value
.Range("I38") = c.Offset(0, 5).Value
.Range("I39") = c.Offset(0, 7).Value
Exit For
End If
Next c
End With
End If

End Sub