Hi,

I have an interesting problem. I have a worksheet where
I want to be able to make the action of changing the
value of cell A1, cause the value of B1 display double
the value of A1. If I type a value in B1 I want A1 to
display half the value of B1 (Actually the calculation is
much more complicated, but just for testing purposes any
relational calculation will do.) When I use the
Worksheet_Change event, it captures the value changes in
one cell and runs the calculation, but that then changes
the value of the adjacent cell which then runs the code
again, etc. I have found that after approx. 220
iterations it finally stops, but this can cause loads of
other problems for me.

To put it simply, how can I tell the worksheet_change
event not to fire if code run from that event causes
other cells to change. Here is a sample of my code below:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim v As Variant
Static x As Long

x = x + 1

Select Case Target.Column
Case 1
With Target
.Font.Bold = True
.Offset(0, 1).Font.Bold = False
.Offset(0, 1) = Target * 2
End With
Case 2
v = Application.Caller
Debug.Print v
With Target
.Font.Bold = True
.Offset(0, -1).Font.Bold = False
.Offset(0, -1) = Target / 2
End With
End Select

Application.StatusBar = x

End Sub

Please note that the x variable is used to check how many
times this event runs before it stops and I have no idea
on how to use the Caller property. I thought that maybe
it could be used to see if the event called itself and
therefore be used to tell it to stop.

Please help...this is driving me nuts.
Many thanks
Vyyk