Call Macro when Cell within Range Changes
 

Hi All,

I understand how to set up a worksheet so that a macro is called when the
value of a specific cell changes (i.e. when Cell A1 changes), but how do you
call a macro when a cell within a range changes (a1:a10).

I know this is simple but I can't find the right arguments?

Any ideas?

Ta

Andi

Call Macro when Cell within Range Changes
 

Andi,

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing
Then
' do something
End If
End Sub

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



"Andibevan" wrote
in message ...
Hi All,

I understand how to set up a worksheet so that a macro is
called when the
value of a specific cell changes (i.e. when Cell A1 changes),
but how do you
call a macro when a cell within a range changes (a1:a10).

I know this is simple but I can't find the right arguments?

Any ideas?

Ta

Andi

Call Macro when Cell within Range Changes
 

Chip,

Thanks that's perfect. I am slightly confused as I have the following code
which runs a macro when the cell range is selected whereas your macro works
when the cell is changes.

I can see what the difference is but don't understand why swapping the range
and the target round makes such a difference.

Regards

Andi

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then
'Call your Macro
Else
End If
End Sub



"Chip Pearson" wrote in message
...
Andi,

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing
Then
' do something
End If
End Sub

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



"Andibevan" wrote
in message ...
Hi All,

I understand how to set up a worksheet so that a macro is
called when the
value of a specific cell changes (i.e. when Cell A1 changes),
but how do you
call a macro when a cell within a range changes (a1:a10).

I know this is simple but I can't find the right arguments?

Any ideas?

Ta

Andi

Call Macro when Cell within Range Changes
 

Andi,

Yours is a SelectionChange event, so will run whenever a cell is selected,
and call you macro if that selection is in A1:A10, whereas Chip's uses the
Change event, so on ly runs when a cell is changed.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in message
...
Chip,

Thanks that's perfect. I am slightly confused as I have the following
code
which runs a macro when the cell range is selected whereas your macro
works
when the cell is changes.

I can see what the difference is but don't understand why swapping the
range
and the target round makes such a difference.

Regards

Andi

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing Then
'Call your Macro
Else
End If
End Sub



"Chip Pearson" wrote in message
...
Andi,

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing
Then
' do something
End If
End Sub

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



"Andibevan" wrote
in message ...
Hi All,

I understand how to set up a worksheet so that a macro is
called when the
value of a specific cell changes (i.e. when Cell A1 changes),
but how do you
call a macro when a cell within a range changes (a1:a10).

I know this is simple but I can't find the right arguments?

Any ideas?

Ta

Andi

Call Macro when Cell within Range Changes
 

Thanks Bob - The answer was staring me in the face. Easy when you know
how..


"Bob Phillips" wrote in message
...
Andi,

Yours is a SelectionChange event, so will run whenever a cell is selected,
and call you macro if that selection is in A1:A10, whereas Chip's uses
the
Change event, so on ly runs when a cell is changed.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Andibevan" wrote in
message
...
Chip,

Thanks that's perfect. I am slightly confused as I have the following
code
which runs a macro when the cell range is selected whereas your macro
works
when the cell is changes.

I can see what the difference is but don't understand why swapping the
range
and the target round makes such a difference.

Regards

Andi

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A10"), Target) Is Nothing
Then
'Call your Macro
Else
End If
End Sub



"Chip Pearson" wrote in message
...
Andi,

Try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing
Then
' do something
End If
End Sub

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



"Andibevan" wrote
in message ...
Hi All,

I understand how to set up a worksheet so that a macro is
called when the
value of a specific cell changes (i.e. when Cell A1 changes),
but how do you
call a macro when a cell within a range changes (a1:a10).

I know this is simple but I can't find the right arguments?

Any ideas?

Ta

Andi