hi ive been trying hard to get the comments box of some cells to populate with the results of a formula from that same cell. I can get it to work for a single cell across multiple worksheets but I cant get it to loop across multiple cells.

Basically I need it to populate cells C7 to AG7 in row 7 across worksheets 5 to 16.

below is what ive been messing around with any help will be greatly appreciated.

thanks

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim WS As Integer

Dim col As Integer

For WS = 5 To 16
For col = 3 To 32



Worksheets(WS).Range("'" & Worksheets(WS).Columns(col) & Worksheets(WS).Rows(7) & "'").ClearComments
With Worksheets(WS).Range("'" & Worksheets(WS).Columns(col) & Worksheets(WS).Rows(7) & "'").AddComment
.Visible = False
.Text Range("'" & .Columns(col) & .Rows(7) & "'").Text
End With
Next
Next
End Sub