Bi-directional formula

[anites]

Hello everyone, I'm currently trying to find a code that would allow me to make a simple operation than works in two ways. I found an old thread from 2011 that answered the question, but I would like to apply the formula to a range of cells and not just the 3 shown in here.

The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Application.EnableEvents = False
   
    If Target.Address(0, 0) = "B2" Then [D2] = Target - [F2]
    If Target.Address(0, 0) = "D2" Then [B2] = Target + [F2]
   
    Application.EnableEvents = True
   
End Sub

Although I would not use this very same formula, the concept is exactly the same, but I need to apply it to a certain range within a column, B2:B33, D2:D33 and F2:F33 to be precise.

I'm new to VBA and I tried everything within my knowledge but I just can't seem to pull this off.

Thanks in avadvance.

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Give this a try with a copy of your workbook

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target, Union(Range("B2:B33"), Range("D2:D33")))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If c.Column = 2 Then
        c.Offset(, 2).Value = c.Value - c.Offset(, 4).Value
      Else
        c.Offset(, -2).Value = c.Value - c.Offset(, 2).Value
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[anites]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Give this a try with a copy of your workbook

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
 
  Set Changed = Intersect(Target, Union(Range("B2:B33"), Range("D2:D33")))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If c.Column = 2 Then
        c.Offset(, 2).Value = c.Value - c.Offset(, 4).Value
      Else
        c.Offset(, -2).Value = c.Value - c.Offset(, 2).Value
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

Thanks a lot Peter, I really appreciate the feedback. It seems to works perfectly fine, now it's only a matter of adapting the formulas a tiny bit

 

[Peter_SSs]

You're welcome. Post back with details if you have trouble adapting.