How about a userdefined function:
Option Explicit
Function EchoComment(FCell As Range) As Variant
Application.Volatile
With Application.Caller
If .Comment Is Nothing Then
'do nothing
Else
.Comment.Delete
End If
If FCell.Comment Is Nothing Then
'do nothing
Else
.AddComment Text:=FCell.Comment.Text
End If
End With
EchoComment = FCell.Value
End Function
You'd use it like this:
=echocomment(a1)
The value in A1 would appear in the cell and the comment would get copied, too.
The application.volatile is there to update the comments if you change them.
(Changing the comment won't make the function run, but it'll catch up with then
next recalculation.)
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
