Worksheet_Change

[Bradley Dawson]

Is there a way to restrict the target range so that the event only happens
if a particular range has a change (not the whole sheet)? I want to execute
code when a particular cell is changed.

 

[Vasant Nanavati]

If Not Intersect(Target, Range("A1")) Is Nothing Then
'run the code
End If

 

[Frank Kabel]

Hi Bradley
the event will get fired for all cells but you can restrict your code
to work only on specific cells. e.g. try something like the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Intersect(Target, Me.Range("B1:B20")) Is Nothing Then Exit Sub
on error goto errhandler
If Target.Value <> "" Then
application.enableevents = false
target.value = "X"
End If
errhandler:
application.enableevents = True
End Sub

This code (changing the value of the cell to 'X') will only get invoked
if you only change ONE cell and this cell is within the range B1:B20

 

[Bob Phillips]

If Not Intersect(Target,Range("A1:H10")) Is Nothing Then

change the range to suit

--

HTH

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

 

[JE McGimpsey]

one way:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
'Do something
End If
End Sub

or, alternatively

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) <> "A1" Then Exit Sub
'Do something
End Sub