running a macro when cemm contents change

[markshowell]

I have four cells a10,a20,a30 & a40 and I want to run a different set o
commands when the contents of any of these 4 cells change i.e. if th
contents of a10 change copy a10 to b10, but if the contents of a2
change copy a20 to b20 and so o

 

[Bob Phillips]

Mark,

Your description is slightly different to your request. Let me clarify. You
say that you want to run different commands depending upon which cell is
change. But what you describe is the same command, but with a different
target, but one that is similaolrly aliugned. SZo in it's simplest form
this will work

This is worksheet code, so it goes in the worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A10,A20,A30,A40")) Is Nothing Then
With Target
.Offset(0, 1).Value = .Value
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

If however, you do need different commands, it is better to structure the
code like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A10,A20,A30,A40")) Is Nothing Then
With Target
Select Case .Address
Case "$A$10": Range("B10") = .Value
Case "$A$20": Range("B20") = .Value
Case "$A$30": Range("B30") = .Value
Case "$A$40": Range("B40") = .Value
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)