Cell Change triggers Worksheet Event
 

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.

Cell Change triggers Worksheet Event
 

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.

Cell Change triggers Worksheet Event
 

Hi

This should do it:

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

If Not Intersect(Target, rng) Is Nothing Then

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

errExit:
Application.EnableEvents = True
End Sub

Regards,
Per

"Charles" skrev i meddelelsen
...
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.

Cell Change triggers Worksheet Event
 

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.

Cell Change triggers Worksheet Event
 

I deliberately dropped the Precedents, couldn't see the point of it.

HTH

Bob

"Rick Rothstein" wrote in message
...
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.

Cell Change triggers Worksheet Event
 

On Jan 8, 12:19*pm, "Rick Rothstein"
wrote:
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.

Thanks to all who replayed. However the solutions provided did not
work for what I was trying to accomplish.
The actual target is the cell value in column S.
I made a change in column Q and the event is triggered, however I need
it to look at the values in column S.
The values in column S will change when any cell value the formula
looks at changes.
This is the formula I have in column S

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


I hope this helps.

Cell Change triggers Worksheet Event
 

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.
.

Cell Change triggers Worksheet Event
 

Then you left the "dot" in there by mistake.<g As for whether the
Precedents property call is needed or not... it might be. What if, in the
most simplest form, you had this in A1...

=B1<""

and in B1 you had this...

=C1<""

If C1 changes, then A1 will change, but the Change event code will not be
triggered if all you check is A1 in the event... you would need to check
A1's Precedents to get the right trigger for the event. Since the OP said
his code for the single cell (which contained a formula) that he checked
the Precedents for worked, I assumed the Precedents property call would be
needed in the modified code as well.

--
Rick (MVP - Excel)


"Bob Phillips" wrote in message
...
I deliberately dropped the Precedents, couldn't see the point of it.

HTH

Bob

"Rick Rothstein" wrote in message
...
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.

Cell Change triggers Worksheet Event
 

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.

Cell Change triggers Worksheet Event
 

Since the values in S2:S75 are formula-derived I would say the Precedents is
required.

At least in my testing it is required.

S2 formula is =A2

Change A2 and receive MsgBoxTarget.Value


Gord

On Fri, 8 Jan 2010 14:22:16 -0500, "Rick Rothstein"
wrote:

Then you left the "dot" in there by mistake.<g As for whether the
Precedents property call is needed or not... it might be. What if, in the
most simplest form, you had this in A1...

=B1<""

and in B1 you had this...

=C1<""

If C1 changes, then A1 will change, but the Change event code will not be
triggered if all you check is A1 in the event... you would need to check
A1's Precedents to get the right trigger for the event. Since the OP said
his code for the single cell (which contained a formula) that he checked
the Precedents for worked, I assumed the Precedents property call would be
needed in the modified code as well.

Cell Change triggers Worksheet Event
 

On Jan 8, 1:54*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Since the values in S2:S75 are formula-derived I would say the Precedents is
required.

At least in my testing it is required.

S2 formula is * *=A2

Change A2 and receive *MsgBoxTarget.Value *

Gord

On Fri, 8 Jan 2010 14:22:16 -0500, "Rick Rothstein"

wrote:
Then you left the "dot" in there by mistake.<g As for whether the
Precedents property call is needed or not... it might be. What if, in the
most simplest form, you had this in A1...

=B1<""

and in B1 you had this...

=C1<""

If C1 changes, then A1 will change, but the Change event code will not be
triggered if all you check is A1 in the event... you would need to check
A1's Precedents to get the right trigger for the event. Since the OP said
his code for the single cell *(which contained a formula) that he checked
the Precedents for worked, I assumed the Precedents property call would be
needed in the modified code as well.

Ok,

I may have made an error in what I posted.
I Apologize for this error. and many thanks for all who responded
However, the cell value derived from the formula that I have in all
cell in Range("S2:S74") is based on streaming data that is in another
cell but same row as the formula.
I'm try to look for the change made in column S to trigger the event.
So, now I'm not sure if this can be done with out a manual change.

Cell Change triggers Worksheet Event
 

On Jan 8, 2:35*pm, Charles wrote:
On Jan 8, 1:54*pm, Gord Dibben <gorddibbATshawDOTca wrote:



Since the values in S2:S75 are formula-derived I would say the Precedents is
required.

At least in my testing it is required.

S2 formula is * *=A2

Change A2 and receive *MsgBoxTarget.Value *

Gord

On Fri, 8 Jan 2010 14:22:16 -0500, "Rick Rothstein"

wrote:
Then you left the "dot" in there by mistake.<g As for whether the
Precedents property call is needed or not... it might be. What if, in the
most simplest form, you had this in A1...

=B1<""

and in B1 you had this...

=C1<""

If C1 changes, then A1 will change, but the Change event code will not be
triggered if all you check is A1 in the event... you would need to check
A1's Precedents to get the right trigger for the event. Since the OP said
his code for the single cell *(which contained a formula) that he checked
the Precedents for worked, I assumed the Precedents property call would be
needed in the modified code as well.

Ok,

I may have made an error in what I posted.
I Apologize for this error. and many thanks for all who responded
However, the cell value derived from the *formula that I have in all
cell in Range("S2:S74") is based on streaming data that is in another
cell but same row as the formula.
I'm try to look for the change made in column S to trigger the event.
So, now I'm not sure if this can be done with out a manual change.

Thought I'd pass this on.
Instead of using a worksheet change event, I should have used the
Worksheet Calculate event.
Still thanks to all who responded to my post.

Cell Change triggers Worksheet Event
 

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.
.

Cell Change triggers Worksheet Event
 

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.

Cell Change triggers Worksheet Event
 

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.


.