Edit after posting:
Note, I see you've cross posted this on other site(s). I will add the attachment when you have included links to everywhere you have cross posted to on all sites involved. Why? See
http://www.excelguru.ca/content.php?184
In the attached:
On
Sheet1:
You range to be monitored D9:I52
A formula in cell L2 (needed to trigger a calculation event)
A button to reset the counts to zero in the result area which is on…
Sheet2:
Range R9:W52 (named
TheCount)
Range D9:I52 containing the same formula as your conditional formatting but referring to
Sheet1 (named
CurrentState)
Range K9:W52 containing a copy of the previous state of D9:I52 (for comparison with CurentState (named
PreviousState)
This is what happens:
Each time any calculation is done on
Sheet1 it triggers a calculation event on that sheet which calls the macro
blah, which:
Puts the the values of the three ranges on
Sheet2 into its own respective array (CS,PS & TC).
Compares each value in CS with each value in PS and if it's different then increment the value in the corresponding TC array by 1.
Updates the values in
PreviousState to those in
CurrentState
Updates the
TheCount range with the values in TC
To use it, start by clicking the reset button to start all counts at zero.
Then let
Sheet1 update itself with the stock data coming in.
You can see the state of play at any time by looking at the
TheCount range at range R9:W52 of
Sheet2.
Note there is code in
Sheet1's code-module and in
Module1.
I'm not sure how this will perform if the data is coming in fast.
