Change Formatting of Comments on Cells

[Alex]

I want to change the formatting of comments on cells in
a pre-existing worksheet. Right now, the formatting is:
Tahoma 8pt. bold font
I want to change it to:
Arial 10pt. (not bold) font

1.How would I change this information for the entire
worksheet or spreadsheet? (I have changed the formatting
on some comments, but not all of them. I am interested in
uniform readable comment formatting. Even if I can apply
a marco to increase all comments font size to 10 pts., I
would be very happy.)

2. What is the way to change this formatting for the
default new spreadsheet template?

Thank you for your consideration.
Alex Bujak

 

[Dave Peterson]

Take a visit to Debra Dalgleish's site:

She has instructions for changing the default format:
http://www.contextures.com/xlcomments02.html

And she has code that I've modified here:
http://www.contextures.com/xlcomments03.html

Option Explicit
Sub CommentModifyFont()

Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
With cmt.Shape.TextFrame.Characters.Font
.Name = "Arial"
.Size = 10
.Bold = False
.ColorIndex = 0
End With
Next cmt

End Sub