Duplication
Thanks for the heads up Tom. I know better but it worked well enough that I
didn't give it a second thought. You never cease to amaze me with how sharp
you are. I sure wish I had your gift.
Best regards,
Greg
"Tom Ogilvy" wrote:
Greg - just a heads up:
If I modify your code with a counter variable to see how many times it is
called, then it looks like this
Private Sub Worksheet_Change(ByVal Target As Range)
Static cnt As Long
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("D1") = Range("A1")
ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
Range("A1") = Range("D1")
End If
cnt = cnt + 1
Debug.Print cnt
End Sub
then I change the value of A1; in the debug window, the last couple of lines
a
200
201
202
203
204
205
(thank goodness it finally stops - <g)
so you might want to make it like
Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
Application.EnableEvents:=False
If Intersect(Target, Range("A1")) Is Nothing Then
Range("D1") = Range("A1")
ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
Range("A1") = Range("D1")
End If
ErrHandler:
Application.EnableEvents:=True
End Sub
--
Regards,
Tom Ogilvy
"Greg Wilson" wrote in message
...
The code I gave you allows you to change or delete either the value in A1
or
D1 and the change will be reflected in the other cell - i.e. the behavior
is
bidirectional. Although it doesn't sound like this is what you want, I
will
first describe how to implement it assuming you are not familiar.
Instructions:
1) Change the cell references to suit in the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("D1") = Range("A1")
ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
Range("A1") = Range("D1")
End If
End Sub
2) Copy the code.
3) Hold the mouse pointer over the sheet tab and right mouse-click.
4) Select View Code. This will take you to the sheet's code module.
5) Paste the code.
6) Test it out: Change or delete the values in either cell. Note that the
code also alows you to select multiple cells inclusive of either of the
above
and delete them and it will still work. It was deliberately written to
handle
this situation.
All of the above said, it sounds as though you only need a simple
worksheet
formula (or formulae). If you only intend to be changing the value in cell
A1
and never in D1 but you want D1 to reflect A1's value, then, as the others
have already provided, the formula: "=A1" or "=If(A1 = "", "", A1) should
do.
If this doesn't do what you need then I suggest posting the formula(e) and
cell address(es).
Regards,
Greg
"Wonderer" wrote:
Ok, what I have is cell A1 and F1 are used in equations. They need to be
exactly the same, at all times. I want it simply so that I can change
the
amount in A1 and it will automatically change the amount in F1 and the
subsequent equation will reflect that change. I am not "literate" in the
code and terminology, so please explain it so a beginner can understand.
Thank you
Steven Shelton
There are two secrets to success in life
1) Never tell anybody everything you know
"Greg Wilson" wrote in message
...
Assuming A1 and D1 are the cells and you want to allow change in
either
cell:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("D1") = Range("A1")
ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
Range("A1") = Range("D1")
End If
End Sub
Regards,
Greg
"Wonderer" wrote:
I need to know how to have two cells contain the same information, on
the
same sheet, so that when I change on cell, the other will reflect
that
change. How do I do that?
--
Steven Shelton
There are two secrets to success in life
1) Never tell anybody everything you know
|