Determining Cells Whose Value Changed Through Each Calculate Event

  • Hi all,


    I'm trying to target individual cells whose value has been updated / changed after every Calculate event.


    The worksheet I'm working on is pulling live data from an external source (updates almost every second), and I'm hoping to highlight each cell that has changed with a color code (representing which band it falls in) each time a Worksheet_Calculate() event takes place but I'm not sure how.


    I just want the changed cell to flash once, which I know how using the OnTime method. I just don't know how to determine/trace which cells have changed. *Comparing with previous values cell-by-cell is NOT feasible as the data is too extensive.


    Any solutions?


    Thanks in advance.

  • Re: Determining Cells Whose Value Changed Through Each Calculate Event


    Hi pangolin,


    Thanks for the sample file! However, the method you're using is not feasible as the data is very extensive and it would be impractical to have a copy of all the data and compare it on each calculation.


    The thing is that i've got approx 40 columns (categories) with about >200 rows of data and 35 of the 40 columns are updated from an external source. Recalculations of the categories can be almost every second.
    Problem: 7,000 to 8,000 cells to be verified each second.


    However, not all cell values change with each recalculation (its a stock tracker, so prices don't necessarily change for all stocks every second). Hence, I want to isolate these cells and change the cell color once (i already have the OnTime procedure to handle this) only for those whose values have changed.


    Due to the data matrix size, having a check for All cells will be very slow.


    I know that IsEmpty(...) can check for the initial calculation, but subsequently? how can you single out a single cell within a CurrentRegion whose value has changed from a recalculation?


    Thanks again!

  • Re: Determining Cells Whose Value Changed Through Each Calculate Event


    Hello Justin, this may not be exactly what you want but it should give some ideas. To compare formulas values you need to keep a copy of the previous values somewhere. Example here assumes columns A:AZ possible formulas and uses Columns BA:CZ to store values as well as a static array & conditional formatting to change the cell colours.
    This will change any cell with a formula in columns A:AZ if different to previous calculation.


    The application of the conditional formatting is not really needed if you happy to apply it to all the cells, but for the exercise I have made it only apply to SpecialCells(xlCellTypeFormulas). (to make that work you need to use R1C1 notaion to allow for non contiguous ranges of formulas). You will notice no looping in this code & it will execute in a split second for large ranges.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!