How to change comment indicator color in Excel?
In Excel, after inserting a comment, a small red triangle indicator appears in the upper right corner of the cell. Many Excel users may wonder if the red indicator color can be changed to other colors they like. In this article, I will talk about a workaround to finish this task.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
There is no direct way for us to change the color of the comment indicators quickly and easily, but, the following VBA code can help you to draw a triangular shape overlap each comment indicator with a specific color you need on the active sheet.
1. Activate your worksheet that you want to change the comment indicators color.
2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
3. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Change comment indicator color in active sheet
Sub CoverCommentIndicator() 'Update 20141110 Dim pWs As Worksheet Dim pComment As Comment Dim pRng As Range Dim pShape As Shape Set pWs = Application.ActiveSheet wShp = 6 hShp = 4 For Each pComment In pWs.Comments Set pRng = pComment.Parent Set pShape = pWs.Shapes.AddShape(msoShapeRightTriangle, pRng.Offset(0, 1).Left - wShp, pRng.Top, wShp, hShp) With pShape .Flip msoFlipVertical .Flip msoFlipHorizontal .Fill.ForeColor.SchemeColor = 12 .Fill.Visible = msoTrue .Fill.Solid .Line.Visible = msoFalse End With Next End Sub
4. After pasting the code, press F5 key to execute the code, and all the red triangle indicators are covered by the blue triangles as following screenshots shown:
1. In the above code, you can change the blue color to your like by just changing the color index in the script .Fill.ForeColor.SchemeColor = 12.
2. The triangle shape you add by above VBA will be changed its size when you resize the cell.
3. If you want to remove all the colored triangle shapes immediately, the following VBA code will do you a fovor:
VBA code: Remove triangular shapes over the comment indicators
Sub RemoveIndicatorShapes() 'Update 20141110 Dim pWs As Worksheet Dim pShape As Shape Set pWs = Application.ActiveSheet For Each pShape In pWs.Shapes If Not pShape.TopLeftCell.Comment Is Nothing Then If pShape.AutoShapeType = msoShapeRightTriangle Then pShape.Delete End If End If Next End Sub
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agoMany Excel users may wonder if the red indicator color can be changed to other colors they like. In this article, I will talk about a workaround to finish this task. https://mobdro.ooo/
To post as a guest, your comment is unpublished.· 3 months agotried the above to change the colour of the comment indicator but no luck? When selecting F5 to execute the code nothing happens? Help please, thansk
To post as a guest, your comment is unpublished.· 1 years agoBonjour peut on faire la meme chose sous condition (ex : si le commentaire contient un mot)?
To post as a guest, your comment is unpublished.· 1 years agoThanks for the idea!! Because I'm working in a VBA/macro free environment (security constraints), I just created a tiny little triangle shape and put it on top of all the bright red indicators, setting the properties to move but don't size with cells. Worked like a charm :-)