On Jan 8, 1:08*pm, Ryan H wrote:
Just so I understand what you are wanting. *If a cell (the Target) is changed
and is linked to any of the cells in Range("S2:S75") you want to "do
something" to any cell within that range that doesn't equal 0 then show the
value of that cell, right? *If so, this code should help. *Hope this helps! *
If so, let me know, click "YES" below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range
Dim rng As Range

* * Set MyRange = Range("S2:S75")

* * If Intersect(Target, MyRange.Precedents) Is Nothing Then
* * * * Exit Sub
* * End If

* * Application.EnableEvents = False

* * ' scan each rng in MyRange
* * For Each rng In MyRange
* * * * If rng.Value < 0 Then
* * * * * * ' do something
* * * * * * MsgBox rng.Value
* * * * End If
* * Next rng

* * Application.EnableEvents = True

End Sub
--
Cheers,
Ryan

"Charles" wrote:
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.
.

Ryan,

I'm looking for any value change in column S (Range "S2:S74"). If any
cell value in column S changes then it should trigger the Event.
The latest code you proved works, but can it not be done with out the
For/Next loop.