In xl2003 at least, copying a single cell to either A1 or C1 caused the
change event to run.

Similarly in my xl2000, also Cut

None of the three examples catered for all situations of how a cell might be
changed, particularly if changing multiple cells, eg pasting or deleting
(incl Cut) and the first selected cell (ie activecell) is not A1 or C1. Mine
catered perhaps for more scenarios using Target(1) but only because the
cells in question were in row 1.

Following attempts to equalise B & D in rows 2-20 even if the active cell is
not in these ranges at time of change and/or multiple cells changed in one
go, overkill !

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, c As Long

' On Error Resume Next
Set Rng = Intersect(Target, Range("B2:B20"))

If Not Rng Is Nothing Then
c = 2
Else
Set Rng = Intersect(Target, Range("D2:D20"))
If Not Rng Is Nothing Then
c = -2
End If
End If

If c Then
On Error GoTo ErrH
Application.EnableEvents = False
Rng.Offset(0, c).Value = Rng.Value
End If

ErrH:
Application.EnableEvents = True
End Sub

Shaun - for your particular purposes change the ranges to "A1" & "C1"
respectively

Regards,
Peter T

PS hope I've included "Sub" after "End" this time !


"Tom Ogilvy" wrote in message
...
Just some added info:

In xl2003 at least, copying a single cell to either A1 or C1 caused the
change event to run.

with my code, copying a multicell range to a destination that included A1
or
C1 would cause it not to run because of the If Target.count 1 then exit
sub in my code.

It really depends on what you want to account for whether that line is
useful or not.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote in
message ...
Tom's code is probably the best of the bunch (I should have done a count
of
the target cells as Tom's code does.)... I would go with that. It may be
important to note that values pasted into A1 or C1 will not trigger the
macro. Just a warning.
--
HTH...

Jim Thomlinson


"Shaun" wrote:

Hi,

Thanks to Peter T, Tom Ogilvy & Jim Thomlinson for your replies!

All three methods work :D! (Peter T's needs End Sub instead of End and
then it works perfect).

It is very neat to see it autoupdate, this is very useful. I can have
a
master input sheet that has updatable figures now.

Bye,

Shaun