comments tag

[Sharon & Howard]

Hi,
When I create a comment in Excel, the default is a red triangle. I'm
currently working on a spreadsheet that contains several different
comments...is there any way I can change the colour of the triangle so i can
differentiate between 2 sorts of comments.
Cheers
Howard

[Biff]

Hi guys,

May I suggest an alternative ? How about using different
colored borders on those cells or maybe even different
colored backgrounds ?

Biff

>.
>

[David McRitchie]

Hi Howard,
Biff's solution is probably more to your liking and easier to
implement with shading, but another solution might be...

If differentiating the type of comment is that important you
could set up a narrow column next to it , to indicate the type
of comment with a single character. That way someone who
is colorblind can see the difference, and you can filter on
the code which seems important.

The specific answer to your question is that you can't change
the color. You are probably not aware that Excel 2002 has different
colors of triangles in different positions.
Color Triangles in Excel (#triangles)
http://www.mvps.org/dmcritchie/excel/colors.htm#triangles
---
HTH, The searches at the top of my search.htm have been changed
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Biff" <biffi...@comcast.net> wrote ...

> May I suggest an alternative ? How about using different
> colored borders on those cells or maybe even different
> colored backgrounds ?
>

> Howard posted the following...

[Dave Peterson]

Another option may be to put a triangle from the Autoshape collection on the
drawing toolbar on top of the comment indicator.

If you like this idea:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim myTriangle As Shape

Set myRng = Selection

For Each myCell In myRng.Cells
Set myTriangle = ActiveSheet.Shapes.AddShape _
(msoShapeRightTriangle, 0, 0, 0, 0)
With myTriangle
.Name = "TRI_" & myCell.Address(0, 0)
.Top = myCell.Top
.Left = myCell.Left + myCell.Width - 8
.Height = 8
.Width = 8
.IncrementRotation 180#
.DrawingObject.ShapeRange.Fill.Solid
.DrawingObject.ShapeRange.Fill.ForeColor.SchemeColor = 7
.Placement = xlMove
End With
Next myCell

End Sub

Select a bunch of cells and run this macro. You may want to adjust that 8 and
the schemecolor.

--

Dave Peterson
ec3...@msn.com

[David McRitchie]

Displaying the comment won't be as simple, if you can't click on the
red triangle.

"Dave Peterson" <ec3...@msn.com> wrote in message news:3EA9C708...@msn.com...

[David McRitchie]

Sorry, brain dead, comment is displayed when cell is selected.
However, the macro should test to make sure there was a comment
first. So using SpecialCells to reduce range to comments.

Modification of Dave Peterson's macro to overlay red comment
triangles with triangles of another color.

Sub ColorCommentTriangels()

Dim myRng As Range
Dim myCell As Range
Dim myTriangle As Shape

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeComments))
If myRng Is Nothing Then
MsgBox "No comments within selection"
Exit Sub
End If
'--On Error GoTo 0 -- would stop reruns

For Each myCell In myRng

Set myTriangle = ActiveSheet.Shapes.AddShape _
(msoShapeRightTriangle, 0, 0, 0, 0)
With myTriangle
.Name = "TRI_" & myCell.Address(0, 0)
.Top = myCell.Top
.Left = myCell.Left + myCell.Width - 8
.Height = 8
.Width = 8
.IncrementRotation 180#
.DrawingObject.ShapeRange.Fill.Solid
.DrawingObject.ShapeRange.Fill.ForeColor.SchemeColor = 7
.Placement = xlMove
End With
Next myCell
End Sub

"David McRitchie" <dmcri...@msn.com> wrote ...

> Displaying the comment won't be as simple, if you can't click on the

> red triangle. [ignore comment was in error]

[Dave Peterson]

And to enhance David's enhancement. You may want to add different triangles to
different color comments in cells in the selected range. You can be very
careful and run the sub again or the sub can check to see if the cell already
has a triangle in it. If it finds one, it can skip it.

Option Explicit

Sub ColorCommentTriangels()
Dim myRng As Range
Dim myCell As Range
Dim myTriangle As Shape

Dim myShape As Shape
Dim myCellHasTriangle As Boolean

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeComments))

On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No comments within selection"
Exit Sub
End If

For Each myCell In myRng

For Each myShape In ActiveSheet.Shapes
myCellHasTriangle = False
If myShape.Type = msoAutoShape Then
If myShape.AutoShapeType = msoShapeRightTriangle Then
If myShape.TopLeftCell.Address = myCell.Address Then
myCellHasTriangle = True
Exit For
End If
End If
End If
Next myShape

If myCellHasTriangle Then
'do nothing
Else

Set myTriangle = ActiveSheet.Shapes.AddShape _
(msoShapeRightTriangle, 0, 0, 0, 0)
With myTriangle
.Name = "TRI_" & myCell.Address(0, 0)
.Top = myCell.Top
.Left = myCell.Left + myCell.Width - 8
.Height = 8
.Width = 8
.IncrementRotation 180#
.DrawingObject.ShapeRange.Fill.Solid
.DrawingObject.ShapeRange.Fill.ForeColor.SchemeColor = 7
.Placement = xlMove
End With

End If
Next myCell
End Sub

--

Dave Peterson
ec3...@msn.com

[David McRitchie]

As a further enhancement to Dave Peterson's macro...

Would suggest an InputBox to select default colorindex (7)
but indicate the current color choices within the fill color palette
and the font color palette. Skipping because don't know how
to find what colors are chosen in the palettes.
(The Original Poster only wanted red and one other color though)

======= Deleting Shapes ==============
With all the color triangles added you probably want to remove
the old triangles, as some point, and certainly when testing.

Sub delShapesOnSht()
'Dave Peterson, misc 2001-11-12, no loop required
ActiveSheet.Shapes.SelectAll '*** warning DELETE all Shapes
Selection.Delete
End Sub

Sub delShapesSel()
'Delete shapes within selection range,
' Dave Peterson, programming, 2001-11-13
Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes
If Intersect(myshape.TopLeftCell, _
Selection) Is Nothing Then
'do nothing
Else
myshape.Delete
End If
Next myshape
End Sub

Code to restrict for Particular Shapes:
If myshape.AutoShapeType = msoShapeRightTriangle Then myshape.Delete

More information on shapes and colors
http://www.mvps.org/dmcritchie/excel/shapes.htm
http://www.mvps.org/dmcritchie/excel/colors.htm
---
HTH,

David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave Peterson" <ec3...@msn.com> wrote in message news:3EAA7198...@msn.com...

[David McRitchie]

John Walkenbach posted an alternative to not being able
to extract the current selection color from the color palette
dialog boxes in his
A Color Picker Dialog Box
http://www.j-walk.com/ss/excel/tips/tip49.htm
which goes beyond keeping everything within a macro
as the solution uses a form.

So the InputBox might better just list some choices, but
there is an alternative available should it become really wanted.

---
HTH,
David McRitchie, Microsoft MVP - Excel

My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

"David McRitchie" <dmcri...@msn.com> wrote =...