How to create a two way formula

[nleahcim]

I want to be able to create formulas where the output can act as the input. For example, I tried to write a simple proof of concept script where you can convert between pounds and kilograms. So if you type in a value for pounds, it'll update the kilograms cell. And if you type in a value for kilograms - it'll update the pounds cell.

This is what I came up with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static PreviousCell As Range
    
    If Not (PreviousCell Is Nothing) Then
        If PreviousCell = Range("H4") Then
            Range("I4").Value = Range("H4").Value * 2.20462262
        ElseIf PreviousCell = Range("I4") Then
            Range("H4").Value = Range("I4").Value / 2.20462262
        End If
    End If
    Set PreviousCell = Target
End Sub

Problem is that it doesn't fully work. It works perfectly as long as you don't enter in a value for pounds, then try to enter in the same value for kilograms. I can't figure out why though!

Also - I'm very new to writing code for Excel (more of a C programmer myself...) - so if there are better ways of doing any of this I'd be all ears!

Thanks!

[StephenR]

Does this work for you? The ratios may be the wrong way round.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Address = "$H$4" Then Target.Offset(, 1).Value = Target * 2.20462262
If Target.Address = "$I$4" Then Target.Offset(, -1).Value = Target / 2.20462262
Application.EnableEvents = True

End Sub

[nleahcim]

Does this work for you? The ratios may be the wrong way round.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
If Target.Address = "$H$4" Then Target.Offset(, 1).Value = Target * 2.20462262
If Target.Address = "$I$4" Then Target.Offset(, -1).Value = Target / 2.20462262
Application.EnableEvents = True

End Sub

Very cool! That works great! I had actually tried something similar to that - but it'd register a couple hundred changes before it would stop executing code. I didn't know about the EnableEvents value! That's just what I needed!

By the way - why don't you need an end if statement? Are those only necessary when you break up the if/then statements on separate lines?

Thanks so much!

[StephenR]

My pleasure.

By the way - why don't you need an end if statement? Are those only necessary when you break up the if/then statements on separate lines?

Yes, if you have only one statement after the Then you can put it on the same line and dispense with the End If.