Showing multiple cells as comments

[RGreen]

Hello

Spreadsheets contains, several rows and columns
For example Column ak45:AK56 has data,
In a different part of the spreadsheet, I stated in ss33 "=ak54",
What I like is a macro, when my cell ss33 is active, I would like to see the
information from ak45:AK56

If I in su49 which I may have stated su49 "=al74, I would like to see the
information from al65:al56

I was playing around with Gord's macro, but it only reference,
onecell.offset(0,1), how Can I see the whole range in a comment?

Thanks,
Rick

 

[RGreen]

correction in my second example the range I would like to see as comments is
al65 to al76.

basically I have data in Column A1:a7 and when I go to g15 I would like to
see what is in a1:a7 as a comment.

and If I go to g16 I would like to see what is in a8:a15 as a comment.

and if I go to h15 I would like to see what is in b1:b7 as a comment.

Please help as this dynamic type of stuff gets me confused.

Thanks,

Rick

 

[Billy Liddel]

Try these in the worksheet code tab

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lRow As Long, lCol As Long
Dim nRows As Integer, lrngRow As Long, lrngCol As Long
Dim rng2Show As Range
Dim c As Variant, msg As String
Dim cmt As Object

For Each cmt In ActiveSheet.Comments
cmt.Delete
Next
lRow = ActiveCell.Row: lCol = ActiveCell.Column

If lRow <= 14 Or lCol < 6 Then
Exit Sub
ElseIf lRow > 14 And lCol >= 6 Then
lrngRow = lRow - 14
lrngRow = lRow + 7
Set rng2Show = Range(Cells(lRow, lCol - 6), Cells(lrngRow, lCol - 6))
End If

For Each c In rng2Show
If Not IsEmpty(c) Then
msg = msg & c & vbLf
End If
Next
msg = Left(msg, Len(msg) - 1)

With ActiveCell
'.ClearComments
.AddComment
.Comment.Text Text:=msg
.Comment.Visible = True
End With

FormatComment

End Sub

Sub FormatComment()
ActiveCell.Comment.Shape.Select True
Selection.ShapeRange.ScaleHeight 1.76, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 0.41, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.88, msoFalse, msoScaleFromTopLeft
ActiveCell.Select
End Sub


Regards
Peter A