Difficulty coming up with the right VBA code to make comment boxes on one worksheet automatically update from values on another worksheet

[carolinemorris79]

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), and 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 or private_sub_worksheet_calculate but I can't see to find the right formula that takes the whole range.

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