I am using EXCEL 97.

I have used the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

But I am getting a debug prompt regularly.

I want monitor changes to only a range of B9:AC13.

Is there a way to set it so that it only monitors this part and if so can
anyone assist me please?


--
Mark

one way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B9:AC13")) Is Nothing Then
'Do whatever
End If
End Sub

Note that Target returns the Selection, so if you have multiple cells
selected, you need to deal with that. For instance, if B1:J10 is
selected, the code above will perform your code steps. One way to
mitigate this would be to restrict the macro to fire only when one cell
is selected:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("B9:AC13")) Is Nothing Then
'Do whatever
End If
End Sub

A different way would be to restrict Target to the desired cells:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set Target = Intersect(Target, Range("B9:AC13"))
If Not Target Is Nothing Then
'Do whatever
End If
End Sub

note that in this case, there's no guarantee that the cell changed is
within your desired range.


In article ,
"Mark" wrote:

I am using EXCEL 97.

I have used the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

But I am getting a debug prompt regularly.

I want monitor changes to only a range of B9:AC13.

Is there a way to set it so that it only monitors this part and if so can
anyone assist me please?

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B9:AC13")) Is Nothing Then
With Target
'do something
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

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

"Mark" wrote in message
...
I am using EXCEL 97.

I have used the following:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

But I am getting a debug prompt regularly.

I want monitor changes to only a range of B9:AC13.

Is there a way to set it so that it only monitors this part and if so can
anyone assist me please?


--
Mark