Font Colour in Comment

[AlexanderBB]

I believe this changed in Excel 2007 (from 2003) where this worked

 .Characters(1, l).Font.Color = RGB(153, 51, 0)

I thought it was now .Font.ColorIndex but that didn't work either.
Also, is there any way to debug.print all elements before ".Characters" to check what they are?

Thanks.

 

[Michael M]

what is the code before this line ?
this works for me on a cell !

Range("A1").Characters(1, 1).Font.Color = RGB(153, 51, 0)

But it appears you have Characters(1, L)...not 1,1 as is required

 

[AlexanderBB]

What color was the text ? If you try Blue RGB(,0,0, 255) instead of Blue I get a sort of goldy brown!

Here's the whole thing:

Sub DoTheComment(r As Range, t, ApplyCol, Optional theSize = 10)
Dim l As Integer

'### EXAMPLE
'Call DoTheComment(Worksheets("Sheet1").Range("J31"), "This is my Comment" & vbLf & "Line 2", Colour)

If Right(t, 1) = vbLf Then t = Left(t, Len(t) - 1) 'Remove any end linefeed
l = InStr(t, vbLf)
With r
    If Not .Comment Is Nothing Then
        .Comment.Delete
    End If
  .AddComment t
    With .Comment
        With .Shape
            .AutoShapeType = msoShapeRoundedRectangle
            With .TextFrame
                With .Characters.Font
                    .Name = "Tahoma"
                    .size = theSize
                    .Bold = False
                End With
                .AutoSize = True
                .Characters(1, l).Font.size = 10
                If ApplyCol Then 'Blue/red 
                     .Characters(1, l).Font.Bold = True
                     .Characters(1, l).Font.Color = RGB(0, 0, 255) 'Blue
                    '.Characters(1, l).Font.Color = RGB(220, 28, 50) 'Red
                 Else
                    .Characters(1, l).Font.Bold = False
                    .Characters(1, l).Font.Color = RGB(0, 0, 0) ' Black
                End If
            End With
        End With
        .Visible = False
    End With
End With
End Sub

The L variable sets the first line bold and a highlight color, red or blue, with Lines 2 on black.
Tests ok in Excel 2003. but not 2007. It may be just the RGB part of it... I'm still playing!

 

[Michael M]

This

Range("A1").Characters(1, 1).Font.Color = RGB(0, 0, 255)

gives me a blue 1st character in excel 2003,2007,2013 !!

 

[AlexanderBB]

RGB(0,0,255) give me that browny grey...
RGB(255,0,0) gives green.

Perhaps it's Windows 10 ?

 

[Michael M]

RGB(255,0,0)
is RED for me, so you could be correct regarding windows version !!
The joys of upgrades.....'specially if you don't want / need them !!

 

[AlexanderBB]

So true, but even so... odd.
Gives correct Red/blue in Excel 2003/Win XP
Wrong colours in Excel 2007/Win 10

Same workbook. Not sure which way to jump now... out the window perhaps?

Maybe try a routine to read the colours?? See if Excel thinks it's red / blue even when it's not?

 

[Michael M]

Why not check the colour palette for the RGB numbers in the later version you actually need and change the code to suit !

 

[AlexanderBB]

Would RGB values very though ?
I've found changing to .ColorIndex 3 or 5 gives red/blue now.
But it doesn't explain why it worked for you.

 

[Michael M]

apart from the fact I'm still using Win7 premium ??