Two Cell Loop

[amirstal]

I want to reflect the value of one number in terms of another.
For example Euros in terms of Dollars.
So in one cell I put the number of Euros and multiply them by the
exchange rate of 1.33 to get the amount in Dollars.
But now I want to put the number of Dollars and use the same exchange
rate to find out how many Euros those Dollars worth.
How can I make a cell to use a formula and also to be able to take a
number without erasing the formula that is in it?

A1 B1 C1

100 Euros 1.33 =A1*B1 which is straight forward

But now I want also to be able to put 100 Dollars in cell C1 and have
the equivalent amount in Euros in cell A1.

Thanks,

 

[Don Guillett]

right click sheet tab>view code>insert this. I suspect you want for more
than ONE set.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" Then _
Target.Offset(, 2) = Target.Offset(, 1) * Target
If Target.Address = "$C$1" Then _
Target.Offset(, -2) = Target / Target.Offset(, -1)
Application.EnableEvents = True
End Sub

 

[Don Guillett]

Please keep questions in the ng unless invited or you want to become a
paying customer.<G>

Private Sub Worksheet_Change(ByVal Target As Range)
if target.row <2 then exit sub
Application.EnableEvents = False
If Target.Column = 1 Then _
Target.Offset(, 2) = Target.Offset(, 1) * Target
If Target.Column = 3 Then _
Target.Offset(, -2) = Target / Target.Offset(, -1)
Application.EnableEvents = True
End Sub

 

[amirstal]

Is that what you meant? Is that how it should look?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" Then _
Target.Offset(, 2) = Target.Offset(, 1) * Target
If Target.Address = "$C$1" Then _
Target.Offset(, -2) = Target / Target.Offset(, -1)
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
if target.row <2 then exit sub
Application.EnableEvents = False
If Target.Column = 1 Then _
Target.Offset(, 2) = Target.Offset(, 1) * Target
If Target.Column = 3 Then _
Target.Offset(, -2) = Target / Target.Offset(, -1)
Application.EnableEvents = True
End Sub


Thanks.

 

[Don Guillett]

The first one works for your ORIGINAL request. The second works for the
entire columns. Did it not work for you? If not, perhaps you did not put in
the SHEET module as instructed. Also, is your calculation mode set to
automatic?