Everytime a cell changes in a range, record the old value as a comment

  • Everytime a cell changes in a named range, I want the code to clear existing comments, then record the old value as a new comment. I have to use Worksheet_Calculate because the cell changes based on a formula. I have tried several, methods, but none are working...

    In this case, it adds a comment to every cell.

    This works only when I change the cell manually.

  • I have found a part solution (placed in the ThisWorkbook module):

    However, I would want this to only look in 3 named ranges instead of the whole sheet, and to add a comment inside the changed cell instead of a message box, something like this:

    Target.AddComment.Text Text:= preValue & Chr(10) & 
    Format(Date, "mm-dd-yyyy") & Environ("UserName")

Participate now!

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