Problem with adding and updating comments from value in VBA

Hello and Greetings,

Thank you for taking the time to read my post; I do appreciate it. I have been struggling with a trying to write a specific VBA code for several days.

Here is a bit more information:

I have a workbook with several worksheet tabs. One of the worksheet tabs contains a column of percentages (that are composed of a VLookup formula from another tab), and another adjacent column with 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 to make a code to have them automatically updated if the percentages change (through the formula). I already tried private sub_worksheet calculate and several versions of sub_worksheet change but I can't see to find the right formula.

Here is the code I have for adding 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

I would greatly appreciate any help or advice anyone is able to provide to me on this issue. Thank you very much for your time!