Changing the Comment Font

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.)

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.