Changing the default font of Excel 2010 comments on a cell

With the following VBA code, you can change the font size and the font of the comments in cells, but it can’t change the font colors. please use the following VBA as:

1. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and then copy and paste the following codes in the module:

Sub FormatAllComments()

Dim ws As Worksheet

Dim cmt As Comment

For Each ws In ActiveWorkbook.Worksheets

For Each cmt In ws.Comments

With cmt.Shape.TextFrame.Characters.Font

.Name = “Times New Roman”

.Size = 14

End With

Next cmt

Next ws

End Sub

In the above code, you can set the.Name and.Size variables as you would like.

2. Then click button to run the code. And then click Review > Show all comments to view the comments, all of the comment formats in the whole workbook have been changed. See screenshots:

---

A handy tool to change all comment formats quickly and time-saving

If you want to quickly change other types of format for all comments, you may try to use the Cell Comment Tools of Kutools for Excel.

Kutools for Excel: with more than 120 handy Excel add-ins, free to try with no limitation in 30 days. Get it Now

After installing Kutools for Excel, please follow the following steps to change the comment formats:

1. Format one comment with the style that you want to apply to other comments.

2. Click Kutools > More > Cell Comment Tools, see screenshot:

3. In the Cell Comment Tools dialog box, click Format tab, and choose the scope that you want to change the comment format, you can choose Active sheet or All sheets. Then click Specify the comment cell button to select the cell which contains the specific comment format you want to apply. See screenshot:

4. Then click Close button to close the dialog box, and the selected cell comment format has been applied to other comments.