VBA Code that records previous value of changed cells in other cells impacts more cells at once than intended

  • Good Day to you all, hope this message find you in good health and spirits.

    I am a rank amateur, cobbling together various code and knowledge from the interweb. Very grateful for this resource and hope someone sees my plight and can help.

    Note: I'm on MacOS and using a VBA Dictionary from GitHub to bypass Microsoft Script Runtime requirement which is working beautifully.


    My intention is to be able to record the previous value of a changed cell (ex. cell with a name of Pick Date) in another cell (ex. cell with a name PAST Pick Date). I can get this to work for 1 cell to 1 cell, but when I try multiple cells to their respective cells with the same code it doesn't work as intended and I lack the expertise to figure this out.


    Code (Visual Basic - record previous value of one changed cell to one other cell.):

    This one watches Column N:N (Pick Date) and records previous value in Column M (13 in the code, PAST Pick Date in my excel table)

    This code works as expected


    Code (Visual Basic - record previous value of multiple changed cells into their respective PAST version.):

    Watches Column N:N (Pick Date) and records previous value in Column M (13 in the code, PAST Pick Date in my excel table)

    Watches Column R:R (ETA Date) and records previous value in Column Q (17 in the code, PAST ETA Date in my excel table)

    Watches Column Y:Y (Update Note:) and records previous value in Column M (24 in the code, PAST Update Note: in my excel table)

    This code does record past values, but overwrites the recorded value into all PAST cells rather than just the corresponding cell as desired. The attached file can be opened so you could see this in action.


    Ideal solution would be to make the Code (Visual Basic - record previous value of multiple changed cells into their respective PAST version.) operate as a 1 to 1 thing with multiple instances, and ideally, would like to know how to replicate this to add more current and past pairs as needed.


    Please let me know if my request is unclear or if there is anything else I can add. I'm new to forum's in general.

    Thank you in advance to whoever lays eyes on this and spends even 1 precious moment. Thank you for your time!




    Source :

    How to remember or save previous cell value of a changed cell in Excel?

Participate now!

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