"KC VBA Qns" wrote:

Hi,

How often have you commented at one cell and then add, delete, resize
rows and columns? When you set all the comments visible the comment
boxes are all over the place. If you have not been disciplined enough
check the "move but dont size with cells" option, this is what you get.

I have codes to capture the comments, delete the comment box,
re-comment with the "move but dont size with cells" option checked.
This wipes up all the original formatting.

Anyone knows how I could preserve the original formatting?

Rgds,

KC

You could use a FormatCopmments macro, this should give you some ideas

Sub FormatComment()
Dim addr As String, r As Long
Dim rng As Range, c As Variant
'there are 3 header lines in this sheet
r = Range("C4").CurrentRegion.Rows.Count + 3
addr = "C" & r
Set rng = Range("C4:" & addr)
For Each c In rng
On Error Resume Next
c.Comment.Shape.Select
Selection.ShapeRange.Height = 12.75
Next
' the last comment is selected, so..
r = Range("A1").CurrentRegion.Rows.Count
Range("A" & r + 1).Select
End Sub

Regards
Peter