If Not Intersect(Target, Range("S2:S75").) Is Nothing Then

You have the "dot" after the range, but you forgot the Precedents keyword
after it...

If Not Intersect(Target, Range("S2:S75").Precedents) Is Nothing Then

--
Rick (MVP - Excel)


"Bob Phillips" wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo errExit
Application.EnableEvents = False
If Not Intersect(Target, Range("S2:S75").) Is Nothing Then

If Target.Value < 0 Then
' do something
MsgBoxTarget.Value
End If
End If

errExit:
Application.EnableEvents = True
End Sub

"Charles" wrote in message
...
HI,

I'm trying to trigger a worksheet event thru a cell or cells value
being changed.
I found this code in this forum and it does what I'm trying to do,
however, I'm trying to have the event activated when a cell value
change within a column. The range I'm looking at is Range("S2:S75").
The values in this range are derived by a formula. Here's the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo errExit
Application.EnableEvents = False
Set rng = Range("s7").Precedents

If Not Intersect(Target, rng) Is Nothing Then

If Range("s7").Value < 0 Then
' do something
MsgBox Range("s7").Value
End If
End If

errExit:
Application.EnableEvents = True
End Sub

This code works, but it only looks at 1 cell, and I need it to look at
the range specified.

Any help or suggestion would be appreciated.