Solved: 2 cells with identical properties - mutually exclusive??

[philh06]

Hi all

first post on the forum so thanks in advance to anyone who can offer some help.

I would like to be able to allow 2 cells (in any one workbook) to have identical properties (thats the best way I can explain)

i.e. if I enter something in the first cell (formula / text / anything) it appears in the other BUT also operate the other way around

To try and make it more clear, if I type a formula in A1 I also want it to appear in E1 but then if I change the formula in E1, I want it to appear in A1

Hope this makes sense

Thanks again

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1,E1" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Address = "$A$1" Then

.Copy Me.Range("E1")
Else

.Copy Me.Range("A1")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[philh06]

thanks for the quick reply, works very well

What about doing this with 2 cells in 2 different worksheets i.e. Sheet 1 A1 linked with Sheet2 E2

[Bob Phillips]

Try this





Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False

If Sh.Name = "Sheet1" Then

If Not Intersect(Target, Sh.Range("A1")) Is Nothing Then

Target.Copy Worksheets("Sheet2").Range("E1")
End If
ElseIf Sh.Name = "Sheet2" Then

If Not Intersect(Target, Sh.Range("E1")) Is Nothing Then

Target.Copy Worksheets("Sheet1").Range("A1")
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[philh06]

perfect :clap: :clap:

thanks very much for your speedy help and advice