CODEWITHSUNDEEP
vbaexcel
TheRealPapa
TheRealPapa

Reputation: 4539

Excel Link two cells in different sheets using when changed macro

I have an Excel workbook with multiple worksheets. I have a cell in WORKSHEET A with range name TRACK1 and a cell in WORKSHEET B with range name TRACK2.

Each TRACK1 and TRACK2 are validated from a list. The user can change either cell from the drop-down list shown when the cell is selected.

I want to be able to allow the user to change either and have the other be also changed to match. Change value of TRACK1 and TRACK2 is changed, and vice versa.

I know how to do this basic macro, but how to stop the event propagating?

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Range("TRACK1")) Is Nothing Then
        Range("TRACK2") = Range("TRACK1")
    End If

    If Not Application.Intersect(Target, Range("TRACK2")) Is Nothing Then
        Range("TRACK1") = Range("TRACK2")
    End If

End Sub

Upvotes: 0

Views: 1144

Answers (1)

YowE3K
YowE3K

Reputation: 23974

In worksheet A's code module, use:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("TRACK1")) Is Nothing Then
        Worksheets("WORKSHEET B").Range("TRACK2") = Range("TRACK1")
    End If
    Application.EnableEvents = True
End Sub

In worksheet B's code module, use:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Application.Intersect(Target, Range("TRACK2")) Is Nothing Then
        Worksheets("WORKSHEET A").Range("TRACK1") = Range("TRACK2")
    End If
    Application.EnableEvents = True
End Sub

Upvotes: 1

Related Questions