Hi,

I have sheet of 550 rows and I should find a way to calculate between the
cells referenced eachother. When I write formulas to each rows sheet warn me
for circular reference and reates a big problem for me. The details:

Columns "H"(rate of Currency) "I"(Currency
Amount) "L"(Amount from Local Currency)

Formula No formula-rate only =($L)/($H)
=($H)*($I)

So Column "I" and "L" dependant to eachother and swap the formula due to the
parameters entered. After putting the current currency rate to Column "H" as
a constant, Column "I" should apply the formula if I enter the Local Amount
to Column "L" or Column "L" automatically calculate the Total (as in the
formula) if I enter an amount to Column "I".

I used an event macro (Worksheet_SelectionChange) but dissapointed when I
wrote formulas for 550 rows and give me the warning of "Too Large Procedure"
.. I should use 4 steps for each row and became too much when I wrote for 550
displayed the first 4 lines below:

If Target.Address = "$I$13" Then Cells.Range("$L$13").Formula =
"=$H$13*$I$13"
If Target.Address = "$L$13" Then Cells.Range("$I$13").Formula =
"=$L$13/$H$13"
If Target.Address = "$M$13" Then Cells.Range("$P$13").Formula =
"=$H$13*$M$13"
If Target.Address = "$P$13" Then Cells.Range("$M$13").Formula =
"=$P$13/$H$13"

By the way my rows are between 13 and 563!

Then I found another from groups written for another kind of circular
reference solution but could not succeed to improve for my required formulas
which is pasted below:

=IF(A1, C1+A1, IF(B1, C1-B1, C1))

Event Macro:
Modify the event macro below to read:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cumCell As Range

If Not Intersect(Target, Range("A:B")) Is Nothing Then
Set cumCell = Range("C" & Target.Row)
Application.EnableEvents = False
With Target
If Left(.Address,2) = "$A" Then
cumCell = cumCell + .Value
.Offset(0, 1).ClearContents
ElseIf Left(.Address,2) = "$B" Then
cumCell = cumCell - .Value
.Offset(0, -1).ClearContents
Else
MsgBox "Select either Column A or B, not both!"
End If
End With
Application.EnableEvents = True
End If
End Sub



Thanks indeed for your help to recover my sheet via VBA to use these
formulas.

Reha