Colours in Comments

[AlexanderBB]

Hi, I want to log what colours are used in various comments in a worksheet and was using
the command

= .Characters(1, 5).Font.Color (I think this gives the color of the first 5 chars and a Null if they're not the same . Is that right ?)

(Don't ask what goes before the first "." I tried to find out here here and never got a reply)

Anyway, it was all going well until suddenly it throws up 74. From Googling around the highest number expected was 56.

The actual color is black, or dark grey.

1) What woud it give an "outside-range" number (if that's what it is) ?
2) Is there list to be found that shows what number > 56 mean?

Thanks !!!

 

[JoeMo]

There are 56 colors on the colorindex palette, but your code asks for color, not colorindex. If you want to stick with the colorindex palette change ".Font.Color" to "Font.ColorIndex".

I think color 74 equates to colorindex 9.

 

[Michael M]

56 would be the limit on Excel 2003 and older, I believe
Maybe this

Sub MM1()
x = Cells(1, 1).Characters(1, 5).Font.ColorIndex
MsgBox x
End Sub

 

[AlexanderBB]

Both .color or .colorindex seem to give the same answer, which is sometimes 74. What colour that supposed to be ?
I don't think it can be 9 as that's brown and what I'm seeing is balck or very dark grey.
Any more suggestions please? I just want to know what the color is and has taken 2 days so far!!

 

[Michael M]

If the color is already in the cell, record a macro copying that cell to another location with all conditions applied.
The recorded macro should give you the color.

 

[AlexanderBB]

Doing that Doesn't seem to even mention the colour Michael...

Range("D1").Select
Selection.Copy
Range("O5034").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sheet1").Select

The copied section contains the comment and the font color looks the same (black)
Running my query stuff on the copied cell gives exact same results and 74 for both color and color index.

RANT = Wish I still had my Commodore 64 and PEEK (XX) would give me the colour... no bull**** like this !!!
What is wrong with Excel ? !!

 

[Michael M]

Have a look at Ricks Blog, there is a code there that will tell you.

Get Displayed Cell Color (whether from Conditional Formatting or not)

 

[AlexanderBB]

That link sortof confused things more - also seems to refer to "Cell" not "Comment".

Perhaps I can offer some that may illustrate my intent - and someone can tell me where I'm going wrong?
My worksheet has a comment on just about every row and most of the results from below are correct, BUT some say 74.
What is this 74 please ??

Sub AttemptIdentifyFontColourinComment()
Dim LineOneLen As Integer
Dim r As Range
Dim ws As Worksheet
Dim cmt As Comment
Dim ThisAddress As String
With Worksheets("AnySheetName")
Set ws = Worksheets(.Name)
    For Each cmt In ws.Comments
        Set r = cmt.Parent
        ThisAddress = Replace(r.address, "$", "")
        Set r = Worksheets(.Name).Range(ThisAddress)
        LineOneLen = Len(Split(cmt.text, vbLf)(0))
        With r.Comment.Shape.TextFrame
            Debug.Print vbLf & ThisAddress
            Debug.Print .Characters(1, LineOneLen).Font.Color
            Debug.Print .Characters(1, LineOneLen).Font.ColorIndex
        End With
    Next
End With
End Sub

 

[Michael M]

Having had a bit of a play it appears that the Default color for the default font placed in the comment by Excel is 74...
When I formatted the font to yellow it appeared as 6
It also appears that 74 is a RGB Color NUMBER not a palette number.... and it's a shade of Dark Brown.........RGB (74,0,0)

 

[AlexanderBB]

Michael, many thanks for your time here !

From about 15000 comments I get 22 that are '74'. Most of those are Brown but not all, some are black and at least one is purple.
I'm kind of stumped... I need to know the colour, and not sure what else to try. If is is the default color, would it change or vary?
And if it is any color would it not be one that's been set and not the default...

BTW all these checks have been done in Excel 2003. I'll try 2007 and see if there's any difference.