How to Identify Long or Hex Colour for Selected Cells with VBA? Use Font.TextColor or Hex(cell.Font.color), or?

Question

How to use VBA excel to report Font Colour Code or Hex for Selected Cells ? The below 1. sequence and 2. Function work great for obtaining Shading Colours. But does not work when substituting the word “Shade” with “Font”, in the third routine below, I was not able to use Font.TextColor nor Hex(cell.Font.color) to identify the font colour. Thumbs up to designer of the Shade Colour Code in 1. and 2. , great if we can do the same in 3. with fonts.

1

Sub ShadClipbdCopyHex_CtlSftT()
'   Got Shade Hex, need font details as well
'   https://stackoverflow.com/questions/76344840/how-to-get-rgb-colors-or-hex-colors-out-of-word-using-vb
    ' Declare variables
    Dim ShadeColor As Long
    Dim fontName As String
    Dim ShadeHex As String
            ShadeColor = ActiveCell.Interior.color
            ShadeHex = ConvertLongToHex(ShadeColor)
'            fontName = ActiveCell.Font.Name
        Clipboard ShadeHex
        Debug.Print ShadeHex ', fontName
End Sub

2

Public Function ConvertLongToHex(lColor As Long) As String
    Dim sRed As String, sGreen As String, sBlue As String
    sRed = Right("00" & Hex(lColor Mod 256), 2)
    sGreen = Right("00" & Hex(lColor \ 2 ^ 8 Mod 256), 2)
    sBlue = Right("00" & Hex(lColor \ 2 ^ 16 Mod 256), 2)
    ConvertLongToHex = "#" & sRed & sGreen & sBlue
End Function

3

Sub FontClipbdCopyHex()
'   CP 45Mar24 Got Shade Hex above, adapting to Fonts substituting Font.TextColor
'   https://learn.microsoft.com/en-us/office/vba/api/Word.Font.TextColor

    Dim FontColor As Long
    Dim fontName As String
    Dim FontHex As String

            FontColor = ActiveCell.Font.TextColor
            FontHex = ConvertLongToHex(FontColor)
'            fontName = ActiveCell.Font.Name
        Clipboard FontHex
        Debug.Print FontHex ', fontName
End Sub

Answer 1

ActiveCell.Font.Color will return a number representing the colour.

Note, however, that it is returning a BGR value, not an RGB value. This seems odd given that colour values are set using the RGB() function, but in fact that function returns a number representing a BGR value.

For example, the following code

Sub test()
    ActiveCell.Font.Color = RGB(0, 0, 255)
    Debug.Print Hex(RGB(0, 0, 255)) & ", " & RGB(0, 0, 255)
    Debug.Print Hex(ActiveCell.Font.Color) & ", " & ActiveCell.Font.Color
End Sub

produces the following in the intermediate window:

FF0000, 16711680
FF0000, 16711680

In this example, the text colour in that cell is blue, not red.