Visual Basic Excel Font Color

Question

I want to determine the color of a current cell. When I

Debug.Print ActiveCell.Font.Color

I get back a number. In this case 24832
How do I determine the proper RGB value of this color given that I only have this number?
Is there some function that turns an int into RGB(0,0,0)?

do you know what binary is, and hex actually is? Convert 24832 to HEX (base 16), is that the color of this font? — Brett Caswell, Jan 11 '15 at 02:14
24832 (base 10) = 0000 0000 0110 0001 0000 0000 (base 2) = 006100 (base 16).. is the font color a dark blue? — Brett Caswell, Jan 11 '15 at 02:16
if I remember correctly.. VBA doesn't do RGB it does like RBG in it's value assignments or something like that.. — Brett Caswell, Jan 11 '15 at 02:19

chris neilsen · Accepted Answer · 2015-01-11T03:01:21.343

5

There is no built-in VBA conversion to RGB, you will need to write your own.

Try This

Function ColorToRgb(Color As Variant) As Variant
    ' return #Value Error for invalid colors
    If Color < 0 Or Color > 16777215 Then
        ColorToRgb = CVErr(xlErrValue)
    End If

    ReDim Res(1 To 3) As Variant
    ' Treating Color as a 24 bit number
    Res(1) = Color Mod 256          ' Red value: left most 8 bits
    Res(2) = Color \ 256 Mod 256    ' Green value: middle 8 bits
    Res(3) = Color \ 65536 Mod 256  ' Blue value: right most 8 bits

    ColorToRgb = Res
End Function

edited Jan 11 '15 at 03:01

answered Jan 11 '15 at 02:07

chris neilsen

52,446
10
84
123

Great. What is the background on that integer value as a color value? The ColorIndex doesn't seem to apply. – Ken Ingram Jan 11 '15 at 02:23
hmm.. can you elaborate on what you're doing here, for completeness. – Brett Caswell Jan 11 '15 at 02:23
Added explanation as inline comments – chris neilsen Jan 11 '15 at 02:31
1

@Ken `ColorIndex` is quite different to `Color`. From Excel VBA help on ColoroIndex: _The color is specified as an index value into the current color palette..._ – chris neilsen Jan 11 '15 at 02:34
I'm not sure about the `ReDim` in `ReDim Res`.. does that require it be dim first in VBA? – Brett Caswell Jan 11 '15 at 02:55
1

@brettCaswell. `ReDim` Can be used instead of `Dim` to declare a dynamic array. (You could `Dim Res as Variant` first if you want, but it is not required. Note: I always use Option Explict in my code) – chris neilsen Jan 11 '15 at 02:58

score 2 · Answer 2 · answered Aug 21 '15 at 05:28

This is a much quicker bit mask for RGB components:

Function RGBComp(Clr As Variant) As Variant
    If Clr >= 0 Or Clr <= 16777215 Then
        RGBComp = Array(Clr And &HFF, (Clr And &HFF00&) \ &H100&, (Clr And &HFF0000) \ &H10000)
    Else
        RGBComp = CVErr(xlErrValue)
    End If
End Function

Visual Basic Excel Font Color

2 Answers2