Did you get my sample code to work like you wanted it too?
--
Cheers,
Ryan


"Charles" wrote:

On Jan 8, 4:53 pm, Ryan H wrote:
Ok, I think I have a better understanding of what you want. You have the
following formula in all cells from S2:S74, right?

IFERROR(IF(J10="----","",IF(J10="Buy",IF(G10<H10,"Stopped","In
trade"),IF(J10="Sell",IF(F10H10,"Stopped",""),"In trade"))),"")

Thus, Col.S is effected by Col. F, G, H, & J. For example, if F10 is
changed and S10 is not equal to 0 you want to "do something", right? If so,
this is the code that will work for you. Hope this helps! If so, let me
know, click "YES" below.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range

' ranges that effect formula in Col.S
Set MyRange = Range("F2:F74,G2:G74,H2:H74,J2:J74")

' if cell that is changed is not in MyRange exit sub else continue code
If Intersect(Target, MyRange) Is Nothing Then
Exit Sub
End If

' disable events to avoid Change event from firing again
Application.EnableEvents = False

' if cell doesn't equal 0 do something and show value
If Cells(Target.Row, "S").Value < 0 Then
' do something
MsgBox Cells(Target.Row, "S").Value
End If

' enable events again
Application.EnableEvents = True

End Sub

--
Cheers,
Ryan

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

Ryan,

Once again thanks for you help. I'll try you code tomorrow when the
Dow is open and see if when any value in the specified range change
which would result a change in the cell value for column S would
trigger the Event. However I'm now in the mind set that a physical
"data" change not an "Streaming" data change must take place.(The
streaming data will be in the cells of column F. I get the data in
column F via a link.


.