Hi, Pinnz,
you could have edited the original post instead of doing a new one.
The macros you use are from the oldest of times - pre 97 when the Worksheet-Events where introduced. I wonder why anybody indicating to use Excel2007 still would work with these macros.
Code goes behind the worksheet you want to monitor, this is for filling in the comments:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngCell As Range
For Each rngCell In Range("C5", Range("C" & Rows.Count).End(xlUp))
If rngCell.Value <> "" Then
With rngCell
On Error Resume Next
.AddComment
.Comment.Text Text:="RT= " & (rngCell.Value * 1)
End With
End If
Next rngCell
End Sub
As you may have only one event of the given name you would have to use a different event for the change of comment text, maybe a double click:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim rngCell As Range
Dim RT As String
For Each rngCell In Range("C5:C" & Range("C" & Rows.Count).End(xlUp).Row)
If Not rngCell.Value = "" Then
With rngCell
If Left(.Comment.Text, 4) = "RT= " Then
RT = Right(.Comment.Text, Len(.Comment.Text) - 4) - .Value
Application.EnableEvents = False
.Value = RT
.Comment.Text Text:="RT= " & RT
End If
End With
End If
Next rngCell
Cancel = True
End Sub
Ciao,
Holger
Bookmarks