View Full Version : Solved: 2 cells with identical properties - mutually exclusive??
philh06
11-19-2008, 06:23 AM
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
11-19-2008, 06:28 AM
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
11-19-2008, 06:47 AM
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
11-19-2008, 10:23 AM
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
11-20-2008, 03:08 AM
perfect :clap: :clap:
thanks very much for your speedy help and advice
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.