• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count number of times colour of cell changed, Excel 2016


New Member
I have excel sheet where i am fetching stock market data, now it changes colour according to my conditional formatting rules, I want to know how many times in total colour has been changed in 1 particular data range during whole day

here below mentioned conditional formatting rule and range is

Any help is appriciated, Thank you


Well-Known Member
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…

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.
Last edited:


Excel Ninja
You should reread Forum Rules:
Ps. Same kind of rules are everywhere .. including cross-posting.