Isso deve começar. Isso funciona com sua foto de amostra - mas precisará de ajustes se a fonte de comentários for uma planilha diferente.
Sub AddDates()
Dim targetRng As Range, commentSrcRng As Range
Dim strPrefix As String ' why this variable? You never seem to use it
Set targetRng = Application.InputBox("Please select the target range. This is the range that will have comments added to each cell", Type:=8)
Set commentSrcRng = targetRng.Offset(0, 3) ' from the screenshot. Will have to tweak if this is a different worksheet.
Dim cel As Range
Dim i As Long
i = 1
For Each cel In targetRng
If cel.Comment Is Nothing Then
cel.AddComment
End If
cel.Comment.Visible = True
cel.Comment.Shape.TextFrame.AutoSize = True
cel.Comment.Text strPrefix & commentSrcRng.Cells(i)
i = i + 1
Next cel
End Sub