Excelguru Help Site - Restore Default Comment Colour

Restores default yellow colour for all comment on the worksheet. Can easily be modified to change all comments to a different colour by changing the value of the lDefaultCommentColor variable.

Where to place the code:
This code goes in a standard module.

Code required:

Code:

Option Explicit
Private Const lDefaultCommentColor As Long = 14811135
Private Sub RestoreCommentColor(wks As Worksheet)
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: To return all comments to the specified color
    Dim cl As Range

    On Error Resume Next
    For Each cl In wks.UsedRange.SpecialCells(xlCellTypeComments)
        cl.Comment.Shape.Fill.ForeColor.RGB = lDefaultCommentColor
    Next cl

    On Error GoTo 0

End Sub

Instructions:
Call the RestoreCommentColor subroutine (passing it a worksheet variable) from within your code, as shown in the examples below:

Code:

Sub RestoreActiveComments()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Restore the Comment colour for the active sheet
    
    Call RestoreCommentColor(ActiveSheet)

End Sub

Sub RestoreAllComments()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Restore the Comment colour for the all sheets
    Dim wks As Worksheet

    For Each wks In ActiveWorkbook.Worksheets
        Call RestoreCommentColor(wks)
    Next wks

End Sub

NOTE: To find out the Long value for the desired colour, first locate an RGB decimal colour code. (An excellent source for this is found here. Enter the following in the immediate window, replacing "0, 255, 0" with your desired colour, and hit Enter:

Code:

?RGB(0, 255, 0)

The resulting number is the Long that you wish to use for your colour constant in the code.

Knowledge Base Articles By Category

Restore Default Comment Colour

Donations

Recent Forum Posts

How to Rename a File from English to Other Language?

Link a series name to a cell using Excel 2010 VBA

How to Rename a File from English to Other Language?

Shared file\macro & IP address

Shared file\macro & IP address