Which is the best method to use

[Ayo]

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

 

[Jim Thomlinson]

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

 

[Ayo]

Thanks so much. Worked great.

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

 

[Ayo]

Spoke too soon. It just worked the first time I tried it. Now it doesn't do
anything. The values are not changing; they just remain the same when I
change Range("B3"). Any ideas?

 

[Ayo]

Never mind Jim, looks like its working again. Thanks for all your help.