VBA & Macro Problems

How to Change the Font Size of All Excel Notes with VBA

Khadija Khan Oct 9, 2026 869 views

Question details

The user wants to increase the default font size of all cell Notes in an Excel worksheet simultaneously.

How to Change the Font Size of All Excel Notes with VBA

Product: Excel
Device & OS: not provided
Scenario: Updating the formatting of multiple legacy cell notes across an active worksheet to improve readability.
Observed behavior: Excel cell notes use a small default font size, forcing users to format each note individually.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) if you plan to keep the macro, and note that this method applies to legacy Notes rather than modern threaded comments.

Solution 1Recommended

Use a VBA Macro to Resize All Notes

Run a short VBA script to automatically loop through and update the font size for all legacy Notes on your active worksheet.

This macro uses the SpecialCells method to find all cells containing comments (legacy Notes) and modifies their TextFrame properties to apply a uniform font size.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.

Insert a Standard Module

Click on 'Insert' in the top menu bar of the VBA Editor and select 'Module' to create a new blank workspace.

Paste the Macro Code

Copy and paste the following code into the module window: Sub ChangeNoteFont() Dim c As Range For Each c In ActiveSheet.Cells.SpecialCells(xlCellTypeComments) c.Comment.Shape.TextFrame.Characters.Font.Size = 12 Next c End Sub

Adjust the Font Size

Locate the number '12' in the pasted code and change it to your desired font size.

Run the Macro

Press F5 or click the 'Run' button (the green triangle) in the toolbar to execute the macro. Close the VBA editor to see the updated notes in your worksheet.

Threaded Comments Limitation: This macro works specifically for legacy Notes. Modern threaded comments in newer versions of Excel have a different structure and cannot be formatted using this specific VBA method.

Manage Spreadsheets and Macros Easily with WPS Office

WPS Spreadsheet offers comprehensive support for macros and VBA, allowing you to run custom scripts like note formatting seamlessly while maintaining high compatibility with Microsoft Excel files.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the notes you wish to resize.
2. Access the Developer Tools: Go to the 'Developer' tab on the top ribbon and click 'Visual Basic' to open the VBA editor.
3. Run the VBA Script: Insert a new module, paste the note-resizing macro provided above, and click Run to automatically update all notes in your sheet.

Full compatibility with Microsoft Excel .xlsx and .xlsm formatsBuilt-in VBA editor for running, editing, and debugging macrosLightweight design with a familiar, easy-to-use interface

microsoft office alternative - wps office

Frequently Asked Questions

Why did the macro not change the font size of my comments?

If you are using a newer version of Excel, you might be using threaded 'Comments' instead of legacy 'Notes'. This macro only targets legacy Notes (xlCellTypeComments). To change threaded comments, a different approach is required.

How do I save my workbook after adding this macro?

To keep the macro for future use, you must save your file as an Excel Macro-Enabled Workbook. Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the file format dropdown menu.

Can I change the font style or color using a similar VBA macro?

Yes, you can modify the macro to change other font properties. For example, you can add 'c.Comment.Shape.TextFrame.Characters.Font.Bold = True' to make the text bold, or adjust the 'Font.Color' property to change the text color.

Excel & Data Problems