Covering Comment Arrow w/ VBA & Matching Cell Color

[vahnx]

I'm attempting to cover the red arrow indicating a comment exists in that cell by drawing a shape in VBA, but I can't seem to get the shape's .SchemeColor to match the exact background .Color property I have the cells set to.

It's a type of grey, Debug.Print'ing that cells background .Color returns 14277081.
The closest I've come is setting .SchemeColor of my shape to 22.

(yes I see my shape isn't exactly covering, will fix in post)

Here's the subroutine:

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Only trigger worksheet change if the previous value doesn't match
    If Not previousValue = Target.Value Then
   
        ' Automatically inserts the date and logger if anything is altered
        Func.UnlockAll
        Application.EnableEvents = False
        
        'Range("B" & Target.Row).Value = Int((999 - 100 + 1) * Rnd + 100)
        'Range("C" & Target.Row).Comment.Text Text:="test"
        
        With Range("C" & Target.Row)
            .Value = Format(Date, "dd/mm/yyyy")
            .ClearComments
            .AddComment CStr(Time)
            .Comment.Shape.TextFrame.AutoSize = True
            shpW = 6
            shpH = 4
            Set shpCmt = Shapes.AddShape(msoShapeRightTriangle, .Offset(0, 1).Left - shpW, .Top, shpW, shpH)
        End With
        With shpCmt
          .Flip msoFlipVertical
          .Flip msoFlipHorizontal
          .Fill.ForeColor.SchemeColor = 22
          .Fill.Visible = msoTrue
          .Fill.Solid
          .Line.Visible = msoFalse
        End With
        Range("D" & Target.Row) = Func.UserName
        
        
        Application.EnableEvents = True
        Func.LockAll
    
        ' When From/To values change on the current worksheet (default only allows DAVTemplate)
        ' the below code will politely error correct the user
        If Not buttonfreeze Then
            If (Not Range("M" & Target.Row) = "" And Not Range("N" & Target.Row) = "") And Not butt******* Then
                If Range("O" & Target.Row) < 0 Then
                    MsgBox ("Meters cannot be negative")
                    Cells(Target.Row, Target.Column).Value = ""
                Else
                    If Not Intersect(Target, Range("M:N")) Is Nothing And Target.Count = 1 Then
                        If (Target = Range("M" & Target.Row) Or Target = Range("N" & Target.Row)) Then
                            If Not Range("O" & Target.Row).Value = 1 Then
                                confirmation = MsgBox("Are you sure?", vbYesNo, "Confirmation")
                                If Not confirmation = vbYes Then
                                    Cells(Target.Row, Target.Column).Value = ""
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub

If anyone knows how I can get my shape to exactly blend in with my color I would appreciate it. Here's a link to my entire .xlsm file. The coding I'm doing is in Worksheet_Change in Sheet1 (DAVTemplate) and can be found by scrolling to the bottom. Typing in any of the white cells will draw the shape in the corresponding C column.

https://dl.dropbox.com/u/2421809/DAV Template.xlsm

PS I know I can alter the red comment arrow in Excel settings but it needs to be done in VBA.

 

[Gary McMaster]

PS I know I can alter the red comment arrow in Excel settings but it needs to be done in <acronym title="visual basic for applications">VBA</acronym>.

Do you mean the setting that suppresses the comment indicator all together?

I'm not really sure what you are after in hiding the indicator but you could turn off the indicator(s) and use some code like the sample below to show the comment only in the selected cell.

Gary

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim oComment As Comment

For Each oComment In ActiveSheet.Comments
    oComment.Visible = False
Next oComment

Set oComment = ActiveSheet.Range(Target.Address).Comment

If Not oComment Is Nothing Then
    Target.Comment.Visible = True
End If

End Sub

 

[Rick Rothstein]

Funny you should ask this question as Debra Dalgleish just posted a blog article about doing this...

Print Numbered List of Excel Comments « Contextures Blog

Here is the code that she references in her article...

Excel Comments VBA

 

[vahnx]

Do you mean the setting that suppresses the comment indicator all together?

I'm not really sure what you are after in hiding the indicator but you could turn off the indicator(s) and use some code like the sample below to show the comment only in the selected cell.

Gary

My mistake, I notice you can't even do it in the settings, you must disable both!
I am trying to get rid of that arrow because it is very unappealing, especially on 1000's of lines.
Basically when you hover over the date the comment shows the exact time.
The problem I'm having is matching the shape color with the type of grey I'm using.