Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell entry triggers Worksheet_SelectionChange event | Excel Programming | |||
thisworkbook.saveAs triggers change event? | Excel Programming | |||
Copying Worksheet triggers Click event of combobox | Excel Programming | |||
Copying Worksheet triggers Click event of combobox on another worksheet | Excel Programming | |||
Click event on cell triggers a macro | Excel Programming |