Hello,
I'm looking for a macro that will dynamically change the comments attached to a range of cells (entering the results from the formula in the cell), triggered by the change in value of another cell.
Specifically...
I'm looking for a macro that will be triggered by any changes to the contents of A2
Once triggered it will update all the comments attached to cells F7:F100, M7:M100, Z7:Z100, AA7:AA100, AB7:AB100, AC7:AC100 so that they include the results from the formula's in their respective cells.
(So once I change A2, the comments in F7 will reflect the results from the formula in F7, and the comments in F8 will reflect the results from the formula in F8, etc...).
I tried this, but I'm really bad at VBA so it didn't work:
Any ideas?
Many thanks,
I'm looking for a macro that will dynamically change the comments attached to a range of cells (entering the results from the formula in the cell), triggered by the change in value of another cell.
Specifically...
I'm looking for a macro that will be triggered by any changes to the contents of A2
Once triggered it will update all the comments attached to cells F7:F100, M7:M100, Z7:Z100, AA7:AA100, AB7:AB100, AC7:AC100 so that they include the results from the formula's in their respective cells.
(So once I change A2, the comments in F7 will reflect the results from the formula in F7, and the comments in F8 will reflect the results from the formula in F8, etc...).
I tried this, but I'm really bad at VBA so it didn't work:
VBA Code:
Sub ValueToComment()
Dim rCell As Range
If Union(Target, Range("A2")).Address = Target.Address Then
Application.EnableEvents = False
Application.ScreenUpdating = False
sResult = Target.Value
Target.ClearContents
For Each rCell In Selection F7:F100, M7:M100, Z7:Z100, AA7:AA100, AB7:AB100, AC7:AC100
With rCell
If .HasFormula Then
On Error Resume Next
.Comment.Delete
On Error GoTo 0
.AddComment
.Comment.Text Text:=CStr(rCell.Value)
End If
End With
Next
Set rCell = Nothing
End Sub
Any ideas?
Many thanks,