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:


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