VBA code to update excel comments when the cell value changes

cmorris9893 Posts 2 Registration date Tuesday March 3, 2015 Status Member Last seen March 5, 2015 - Mar 3, 2015 at 01:22 PM
I have a workbook with several worksheet tabs. One of the worksheet tab contains a column of percentages (that are composed of a VLookup formula from another tab), as a column with relevant text. I want the information from these two columns to appear as comments in a separate worksheet. I would also like them to automatically update if the percentages are updated or if the text is updated. I found a way to insert the comments with the code below, but I don't know how to update them (I presume it is some sort of private sub_worksheet change but I can't see to find the right formula.

Here is what I have (but it won't update the comments; the code only runs once):

Sub ValueToComment()

Set shSource = ThisWorkbook.Sheets("Percentage Table")
Set shDest = ThisWorkbook.Sheets("dashboard2decisionmakers")
Dim lCnt As Long

For Each c In shDest.Range("s2:z2", "s3:z3")
c.AddComment.Text shSource.Range("e3").Offset(lCnt, 0).Value * 100 & "% complete : " & shSource.Range("e3").Offset(lCnt, 3).Value
lCnt = lCnt + 1
Next c

End Sub