Changing the Comment Font

by Allen Wyatt
(last updated August 6, 2022)

4

When Barrie places a comment in a worksheet, the font used in the comment is not to his liking. He would like to change the default comment font to an 11-point Bookman Old Style font. He wonders how he can go about doing this.

Before jumping into specifics, it needs to be pointed out that there is a bit of confusion these days when it comes to the phrase "comments." You see, in the version of Excel provided with Microsoft 365 (as of earlier in 2022), "comments" no longer means what it used to mean for the quarter-century before that. In the most recent parlance, "comments" refers to threaded comments, and traditional comments are now called "notes."

Why is this important? Because, quite honestly, Microsoft's redefinition of traditional, accepted terminology makes it more difficult to talk about comments at all. So, for the balance of this tip, understand that if I mention "comments," I'm referring to what Microsoft now calls comments (the new, threaded version) and when I mention "notes," I'm referring to what we've all known as comments for most of our lives.

It is important to understand notes vs. comments, as well, because it is impossible to do any formatting to comments. You can edit a comment, but if you select the text within a comment, you cannot alter any of its properties—what you see is what you get, and that is that. You can see this pointed out very clearly in this page from Microsoft (click the heading that says "Do comments work differently than notes"):

https://support.microsoft.com/en-us/office/the-difference-between-threaded-comments-and-notes-75a51eec-4092-42ab-abf8-7669077b7be3

In notes, however, you can do formatting. For instance, you can change the font used in an individual note by simply selecting the text within it and then using the formatting tools on the Home tab of the ribbon.

What Barrie is asking for, however, is a way to change the default comment font. I have to assume that Barrie is talking about changing the default font for notes because, well, no changes can be made to the font in comments.

Interestingly enough, there is no way to change, within Excel, the default font used in notes. It is possible to make changes in Windows that may help, but it really depends on the version of Windows you are using. See, for instance, this page from Microsoft:

https://support.microsoft.com/en-us/office/format-worksheet-comments-eb1b7d0a-6fe0-4a0c-b1e6-f1d68e6edda1

If you read through the page carefully, you'll see that in Windows 10 (and, presumably, Windows 11) you can change the font size used on the screen (but not the actual font), which will necessarily affect the appearance of notes—along with everything else—in Excel. Back in the days of Windows 7 and Windows 8 you could change the appearance (including font specifications) of ToolTips, which is what Excel uses for notes.

Even if you could change the appearance of ToolTips in the most recent versions of Windows—and I could find no indication that such was possible—the change would affect not just Excel notes, but also ToolTips in all other programs. If you don't want to change the font used for all the ToolTips on your system, then you are back to changing the font used in individual notes within Excel.

If changing individual notes is not your idea of fun (and it wouldn't be if you have a lot of notes), then you might be interested in a macro that will step through each of the notes in a worksheet and modify the font used.

Sub ChangeNotes()
    Dim cmt As Comment

    For Each cmt In ActiveSheet.Comments
        With cmt.Shape.TextFrame.Characters.Font
            .Name = "Bookman Old Style"
            .Size = 11
        End With
    Next cmt
End Sub

In order to use the macro, you'll need to make sure that the .Name property is set to the name of the font you want, and that the spelling is exact. When you run it, all the comments in the active worksheet will be affected.

Finally, you probably already picked up on the fact that the macro uses the Comments object, even though it is notes that are affected. This is one more example of the confusion introduced by Microsoft in redefining what a "comment" is. (Hard to believe that someone gets paid for making such decisions.)

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12952) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Performing Complex Sorts

One way you can easily work with data in a worksheet is to sort it into whatever order you find most helpful. Excel ...

Discover More

Getting Excel Dates into Outlook's Calendar

If you want to move information from Excel into Outlook, how you do it can be a bit tricky. Here's an example of how you ...

Discover More

Recovered Document becomes Default

Word has a feature called AutoRecover that helps you when Word or Windows crashes. If your Normal template gets messed up ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Adding Comments to Protected Worksheets

When you protect a worksheet, Excel stops users from editing or otherwise making changes to the data in the worksheet. If ...

Discover More

Printing Formatted Comments

When you print your worksheet, you may want Excel to include your comments or notes as they appear on the screen. Here's ...

Discover More

Linking Comments to Multiple Cells

In Excel, single comments are associated with single cells. If you want to have a comment be linked to multiple cells, ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 1 + 0?

2022-08-09 10:11:04

J. Woolley

@Beepee
Use cmt.Shape.Fill.ForeColor.RGB = RGB(..., ..., ...)
My Excel Toolbox includes the following macros for Unthreaded Comments (Notes):
CommentAuthors -- Remove author's name
FormatComments -- Format text, shape, color, and shadow
MoveComments -- Reposition near cell
ResizeComments -- Resize to fit text
UpdateComments -- Run each of the macros above
CommentHyperlink -- Attach a hyperlink
PasteImageInComment -- Paste image from clipboard or file
The following functions apply to Threaded Comments or Unthreaded Comments (Notes):
=CommentText(Target,[SkipAuthor]) -- Return the text
=HasComment([Target],[Threaded],[AllCells]) -- True if Target has comment
The following function will add a background image from a file to an Unthreaded Comment (Note):
=ImageInComment(ImageFile,[Target],[ScaleFactor],[RotateAngle],[NoAuthor])
Microsoft should fire whoever applied the old name to the new feature and gave the old feature a new name. At least they left the old VBA alone.
See https://sites.google.com/view/MyExcelToolbox/


2022-08-07 13:18:18

Beepee

Hi Allen.
Sorry about the duplicate -- if only I knew what I was doing... tring to sent the following update:

I played with your offering and to chang the Font Colour added
.Color = RGB(0, 125, 115)
after the .Size statement which works fine (I was chuffed!)
However I have not had any luck trying to change the Background/Fill colour.

Obviously someone has achived this (comment below from Bob Williams) how can I get hold of this or maybe something more simle:)


Regards
Barrie


2022-08-07 10:51:21

Beepee

Hi Allen.
That's perfect thank you to all who contributed.
Just what I needed.
Please keep the Tips coming.

Regards
Barrie


2022-08-06 10:38:54

Bob Williams

I was delighted to see your Tip on cell comments (sorry, notes now (grrrr!)), as I have been irritated by that for a long time. Also good to know that you found no way to change the default -- another thing I have wasted too much time on! Bur your solution was to change all the comments with a macro. Well, I have already written a macro that does a lot more to specify the appearance of the macros on a worksheet. It even includes a way to specify the position of the note when editing it. The macro uses Defined Names -- I keep them in a separate worksheet for easy changing. Those cells look like this: (see Figure 1 below) . Here's an example of the result of the macro: (see Figure 2 below)
The macro is not short (~16 pages when viewed in Word) and I would happily send you the .bas file but I see no way to do that in this comment.

P.S. Had hard time entering file spec for figures due to overlapping fields.

Figure 1. 

Figure 2. 


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.