Excel linking cells

[Hidden999]

Hi guys,

I am making a worksheet and have a question regarding linking cells.

What I want to achieve:
Having two cells (on different sheets or not) which contain the same text.
So if I change the text in one cell it also changes in the other cell. Vice versa.
I've tried doing it just like this.

A1 contains formula (=B1)
B1 contains formula (=A1)

But then if you type it overwrites the formula.

Thanks in advance!

 

[Fluff]

You need to save the file as an .xlsm file, not an .xlsx file.

 

[Hidden999]

It works!! You are an absolute legend. Cheers mate

 

[Fluff]

You're welcome & thanks for the feedback

 

[Hidden999]

One question, what is easy if you want only certain cells.
For example A1 in sheet1 is the same as D1 in sheet2?

Also how do you add more cells. Like A1 to D1 and C2 to D2

 

[Fluff]

Something like

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Addr As String
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("A1,C2")) Is Nothing Then
      Application.EnableEvents = False
      Select Case Target.Address(0, 0)
         Case "A1"
            Addr = "D1"
         Case "C2"
            Addr = "D2"
      End Select
      Sheets("sheet1").Range(Addr).Value = Target.Value
      Application.EnableEvents = True
   End If
End Sub

This will only work if a single cell is changed.

 

[Hidden999]

Also if the cells are not on the same sheet? For example A1 on sheet1 to D3 on sheet2 and F3 on sheet1 to A3 on sheet2?

 

[Fluff]

The code I posted needs to go in Sheet2 & will modify sheet1, you need the same code in sheet1 with the ranges reversed

 

[Hidden999]

It gives an error 1004? I'm not sure what it is?

 

[Fluff]

What was the message?

 

[Hidden999]

It's in dutch so i'm not sure what it is in englisch but something about a fault in an object. When I press 'look for fault' it indicates to this line: 'Sheets ("ame") . Range(Addr) .Vaue = Target ...