Intersect multiple non contiguous ranges

[PATSYS50]

Hi all, vba beginner here.

The below code is supposed to fill up cell F10 with the value when D10 is entered and vice versa.

But is is giving out error. Can someone point to me what is wrong?

Thanks in advance.

Private Sub worksheet_change(ByVal target As Range)

If Not Intersect(target, Union(Range("D10"), Range("F10"))) Is Nothing Then

Range("D10").Value = (Range("F10").Value - 32) / 1.8
Range("F10").Value = Range("D10").Value * 1.8 + 32

End If

End Sub

 

[Legacy 143009]

Because, when you set a cell value, it triggers a Worksheet_Change event again. And because the changed cell intersects with the target, it fires again and again. One solution will be disabling events for a while.

Private Sub worksheet_change(ByVal target As Range)

  If Not Intersect(target, Union(Range("D10"), Range("F10"))) Is Nothing Then
    Application.EnableEvents = False
    Range("D10").Value = (Range("F10").Value - 32) / 1.8
    Range("F10").Value = Range("D10").Value * 1.8 + 32
    Application.EnableEvents = True
  End If

End Sub

 

[PATSYS50]

Because, when you set a cell value, it triggers a Worksheet_Change event again. And because the changed cell intersects with the target, it fires again and again. One solution will be disabling events for a while.

Private Sub worksheet_change(ByVal target As Range)

  If Not Intersect(target, Union(Range("D10"), Range("F10"))) Is Nothing Then
    Application.EnableEvents = False
    Range("D10").Value = (Range("F10").Value - 32) / 1.8
    Range("F10").Value = Range("D10").Value * 1.8 + 32
    Application.EnableEvents = True
  End If

End Sub

Thank you!

 

[Legacy 143009]

You are welcome