How to change all comment formats in cells in Excel?
When we insert comments in a worksheet, sometimes, we may need to change the default comment formats, if there are a few comments, we can change them manually, but if there are hundreds of comments need to be changed, how do you solve it?
With the following VBA code, you can change the font size and the font of the comments in cells, but it can’t change the font colors. please use the following VBA as:
1. Click Developer > Visual Basic, a new Microsoft Visual Basic for applications window will be displayed, click Insert > Module, and then copy and paste the following codes in the module:
Dim xWs As Worksheet
Dim xComment As Comment
For Each xWs In Application.ActiveWorkbook.Worksheets
For Each xComment In xWs.Comments
.Name = "Times New Roman"
.Size = 14
In the above code, you can set the.Name and.Size variables as you would like.
2. Then click button to run the code. And then click Review > Show all comments to view the comments, all of the comment formats in the whole workbook have been changed. See screenshots:
A handy tool to change all comment formats quickly and time-saving
If you want to quickly change other types of format for all comments, you may try to use the Format Comments of Kutools for Excel.
After installing Kutools for Excel, please follow the following steps to change the comment formats:
1. Format one comment with the style that you want to apply to other comments.
2. Click Kutools > More > Format Comments, see screenshot:
3. In the Format Comments dialog box, click Format tab, and choose the scope that you want to change the comment format, you can choose Active sheet or All sheets. Then click Specify the comment cell button to select the cell which contains the specific comment format you want to apply. Then another dialog pops up to tell you the number of cells changed. See screenshot:
4. Then click Ok > Cancel buttons to close the dialog box, and the selected cell comment format has been applied to other comments.
If you don’t want the use the bold font style in all comment cells, please check Force non-bold formatting option.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Thanks for this bit of code. It worked for me. But, I would also like to stretch the width property so the increase size text will show the way I like it. Recorder records what you see below, but I can't figure out how to get this into the For Each loop. Selection.ShapeRange.ScaleWidth 1.63, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 1.98, msoFalse, msoScaleFromTopLeft
Any advice on how to update the code for this extra little trick?