Hi all,
I am working on a speed critical in play strategy in Excel. I want to execute some code whenever a horses back price changes. I am trying to use Worksheet_Change to identify when a back price changes. The problem I have is that the Target.Address value is always the full range of all the horses, so for 9 horses Target.Address = $A$9:$K$26. This means that my code is executed whenever any cell in that range change. Is there any way round this?
Any help is very much appriciated.
Worksheet_Change Target
Hi Imajik
Theoretically narrowing the target range to just G7:G26 or even a single cell would work. However I have found in practice any refresh, even with the same data, triggers the change event.
There are so many slight differences between version you might get away with this though.
In practice though I think you will need to use the on change, i.e every refresh, to call a Macro to copy/paste some of the R array to another part of the spreadsheet and use an IF condition, presumably NOT EQUAL between the 2 arrays to call another Macro to do what you want.
Obviously you need to have the IF before the copy/paste and you can probably do all this in code rather than pasting on the spreadsheet depending on how much code you want to write.
Theoretically narrowing the target range to just G7:G26 or even a single cell would work. However I have found in practice any refresh, even with the same data, triggers the change event.
There are so many slight differences between version you might get away with this though.
In practice though I think you will need to use the on change, i.e every refresh, to call a Macro to copy/paste some of the R array to another part of the spreadsheet and use an IF condition, presumably NOT EQUAL between the 2 arrays to call another Macro to do what you want.
Obviously you need to have the IF before the copy/paste and you can probably do all this in code rather than pasting on the spreadsheet depending on how much code you want to write.
You can check if specific cells have changed and only act on those - I use the code below to act every time the market countdown (countup for inplay) timer changes ie every second ... seems to work for me ...
Private Sub Worksheet_Change(ByVal Target As Range)
' see if the timer has changed
Dim cellF4 As Range
Set cellF4 = Intersect(Target, Worksheets("Bet Angel").Range("F4"))
If Not cellF4 Is Nothing Then
DoMyThing(cellF4.Value)
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' see if the timer has changed
Dim cellF4 As Range
Set cellF4 = Intersect(Target, Worksheets("Bet Angel").Range("F4"))
If Not cellF4 Is Nothing Then
DoMyThing(cellF4.Value)
End If
End Sub