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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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
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.· 8 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.· 10 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.· 2 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 :-)