• 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:
    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
    Call the RestoreCommentColor subroutine (passing it a worksheet variable) from within your code, as shown in the examples below:
    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:
    ?RGB(0, 255, 0)
    The resulting number is the Long that you wish to use for your colour constant in the code.


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

  • MVP Logo
  • Recent Forum Posts


    Calculate days and cost

    The EDATE function works with months of any length - have a look at that. For example, with a base date of 31/01/2016 in A1, you will get the following:...

    AliGW Today, 02:02 PM Go to last post

    Formula Protection

    Glad to have helped! ...

    AliGW Today, 01:53 PM Go to last post

    Calculate days and cost

    I need to calculate the inclusive days remaining in the first month of a service period, the full months to the end of 30 Sep for each year, and the number...

    brett.white1 Today, 01:46 PM Go to last post

    Formula Protection

    Thanks friend for your supports. You helped me. I understand about that cross-posting and did as was advised to me. ...

    kepler19 Today, 10:08 AM Go to last post

    Formula Protection

    @ Kepler

    please do not quote entire posts unnecessarily. They clutter the thread and make it ahrd to read.


    Pecoflyer Today, 10:02 AM Go to last post