Font used for Excel Notes

Question

I have a large spreadsheet with hundreds of comments (now called Notes) created in an old version of Excel. I am updating this spreadsheet using Excel 365. All the old notes used Tahoma 9 point font. Everything I type in when updating is in MS Sans Serif 10 point font. This looks a mess and changing either the new or the old text to match the other font is a massive job. It appears that I cannot change the default font for new text to match the old font. Has anyone developed a macro that would change all the text in all the notes to the new font?

jkpieterse · Answer

Wrake&nbsp;Sure!Sub UpdateComments()
    Dim cmnt As Comment
    Dim sht As Worksheet
    For Each sht In ActiveWorkbook.Worksheets
        For Each cmnt In sht.Comments
            With cmnt.Shape.TextFrame.Characters.Font
                .Name = "MS Sens Serif"
                .Size = 10
            End With
        Next
    Next
End Sub(Update font name and size as needed)

wrake · Answer

Thanks for that. It works fine. Takes 10 to 15 minutes, but only once per workbook.

jkpieterse · Answer

Perhaps adding Application.ScreenUpdating = False before the start of teh For statement will speed things up a bit.

wrake · Answer

Did not try this as it was reassuring to see the display changing to know that it was working.For anyone else who sees this: note that there are both Sens Serif and Sans Serif fonts. The Sans Serif font is the default for new notes.

Forum Discussion

Font used for Excel Notes

Share

Resources