2 Methods to Batch Change the Fonts of All Comments in an Excel Workbook

After inserting many comments in an Excel workbook, you may want to customize the fonts of all the comments. In this situation, you can refer to this article. Here we will share you 2 easy methods.

As we can see, the comments inserted in Excel automatically use the default font. At times, you may want to alter the comments’ fonts. If there are a few comments, you can simply use the following Method 1 to manually change them. However, if there are hundreds of comments, you are better off selecting the latter means.

Method 1: Change One by One

1. First off, select a comment in the worksheet.
2. Then, right click on it and select “Format Comment” from the context menu.

   

3. Next, in the small popup dialog box, you can select fonts and styles to your liking.

   

4. Ultimately, click “OK”.
5. Later, you can use this way to manually change comments one by one.

Method 2: Batch Change via VBA

1. For a start, launch Excel VBA editor with reference to “How to Run VBA Code in Your Excel“.
2. Then, in the “Microsoft Visual Basic for Applications” window, put the VBA code below into a project or module.

Sub BatchChangeFontsOfAllComments()

    Dim objWorksheet As Excel.Worksheet

    Dim objComment As Excel.Comment

   

    'Process all worksheet one by one

    For Each objWorksheet In ThisWorkbook.Worksheets

        If objWorksheet.Comments.Count > 0 Then

           For Each objComment In objWorksheet.Comments

               With objComment.Shape.TextFrame.Characters.Font

                    'Specify font name and size

                    .Name = "Comic Sans MS"

                    .Size = 10

                    'Use RGB function to create a color value

                    .Color = RGB(255, 0, 0)

                    .Italic = True

                    .Bold = True

               End With

           Next

        End If

    Next

End Sub

 

3. Later, run this macro simply by pressing “F5” key button.
4. Eventually, after macro finishes, back to the worksheets.
5. As you can see, the fonts of all comments have been changed, as shown in the following screenshot:

Comparison

	Advantages	Disadvantages
Method 1	Quick and easy to process a small amount of comments	Very troublesome in case of too many comments
Method 2	Very efficient regardless of the number of comments	Increase risks of virus infection

Restore Compromised Excel File

When your computer suddenly shut down, if some Excel files haven’t been saved, the next time when PC boots back up, you may find the Excel files are corrupted. At this point, if you would like to get back the files, you have no choice but to take aid of a powerful and reliable Excel recovery tool, like DataNumen Excel Repair, which can recover corrupted Excel data with effortless ease.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including fix sql and outlook repair software products. For more information visit www.datanumen.com