Workshet onchange
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?
|