Allow users to change the color of the comment indicator color so.

[Guest]

I have some reporting Excel files that color numerical cells against
established criteria. If I have comments in the cell, and the cell becomes
red, I need the comment indicator to turn white or allow me to change the
color to one that is not used for evaluations.

 

[Debra Dalgleish]

There's sample code here to add coloured shapes over the comment markers:

http://www.contextures.com/xlcomments03.html#Indicator

You could modify it to add a marker to red cells only, e.g.:

'=================================
Sub CoverCommentIndicatorRedCells()
Dim ws As Worksheet
Dim cmt As Comment
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'shape width
Dim shpH As Double 'shape height

Set ws = ActiveSheet
shpW = 6
shpH = 4

For Each cmt In ws.Comments
Set rngCmt = cmt.Parent
If cmt.Parent.Interior.ColorIndex = 3 Then
With rngCmt
Set shpCmt = ws.Shapes.AddShape(msoShapeRightTriangle, _
rngCmt.Offset(0, 1).Left - shpW, .Top, shpW, shpH)
End With
With shpCmt
.Flip msoFlipVertical
.Flip msoFlipHorizontal
.Fill.ForeColor.SchemeColor = 12 'yellow marker
.Fill.Visible = msoTrue
.Fill.Solid
.Line.Visible = msoFalse
End With
End If
Next cmt

End Sub
'==================================