Try something like

If Target.Address = Cells(10,39).Address Then


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Roger PB" wrote in message
...
Tom Ogilvy gave a helpful answer regarding a query by Nigel
Bennett.

I have been trying to adapt this answer so that when a value is
entered in a particular cell, it is replicated elsewhere in a
range.
This works fine as long as I define the cell in a line like
'If Target.Address = "$I$39" Then

However, I want to vary the target address, using variables and
loops, so entered, as a trial attempt

If Target.Address = Cells(10, 39) Then....
or alternatively
If Target.Address = Worksheets("Sheet3").Range(Cells(10, 39))
Then
If Target.Address = Worksheets("Sheet3").Range(.Cells(10, 39))
Then
All produced an error message when I ran the routine.

However,the line
Set rng = Worksheets("Sheet3").Range(Cells(25, 25), Cells(27,
27)) created no problem.
I cannot determine the correct syntax, or when one needs the
dots, and where they should be put, or when one can safely omit
them .

Tom also wrote
What triggers the macro, entering a value....?

If so the right click on the sheet tab of the menu sheet and
select view
code. Paste in code like this:

This was a new technique for me.
Normally I record a macro, assigning a keyboard shortcut, then
edit the macro, pasting in the code I have found in the
newsgroup.
Since Tom's code runs automatically, I guess there is no need
to assign a shortcut. But I am not clear when one attaches a
routine to a worksheet, and when one puts it in a module. Or
how to assign a keyboard shortcut without using the macro
recorder.

Any help would be appreciated. I have two books on Excel VBA
programming, but neither is very helpful on the above issues.

Rogerpb