How to keep two cells in a excel in sync by cross linking

Hi!

Below you see a previous thread with a similar problem to mine:

Now, my question is: I have the exact same problem, but how do I change the code when the two numbers I want synced are on differen worksheets (but same workbook).

I appreciate if somebody could help me! Thanks!


" 05-05-2006, 07:22 AM
srinu1264
Registered User Join Date: 05 May 2006
Posts: 11

How to keep two cells in a excel in sync by cross linking

Hi , I want to keep two cells in sync with the same data. The problem with the link option is that we can have one source and any target . But I am interested that same value be shared across two cells so that if one cell updates the value then the other dependent cell automatically updates its data.

Please let me know how can I do this?

-----------------------------------------------------------------

If both cells are in the same Worksheet, the following code should be pasted
in worksheet's code (right-click on tab name and select Code)

I used A1 and A2 as example addresses. You can modify these.

HTH
--
AP

'------

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell1 As Range
Dim rCell2 As Range
If Target.Count > 1 Then Exit Sub
Set rCell1 = Range("A1")
Set rCell2 = Range("A2")
Application.EnableEvents = False
Select Case Target.Address
Case rCell1.Address
rCell2.Value = rCell1.Value
Case rCell2.Address
rCell1.Value = rCell2.Value
End Select
Application.EnableEvents = True
End Sub

'-------
"

Maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rArea   As Range

    For Each rArea In Target.Areas
        Worksheets("Sheet2").Cells(rArea.Address).Value = rArea.Value
    Next rArea
End Sub

Code goes in the appropriate sheet module.

If the effect is to be bidirectional, put the same code in the other sheet module with Sheet2 changed appropriately.