Setting a font color in VBA

Question

Hi.

I want to set the font color of a cell to a specific RGB value.

If I use

ActiveCell.Color = RGB(255,255,0)

I do get yellow, but if I use a more exotic RGB value like:

ActiveCell.Color = RGB(178, 150, 109)

I just get a grey color back.

How come can't I just use any RGB value? And do you know any workarounds?

Thanks.

Answer 1

Excel only uses the colors in the color palette. When you set a cell using the RGB value, it chooses the one in the palette that is the closest match. You can update the palette with your colors and then choose your color and that will work.

This will let you see what is currently in the palette:

 Public Sub checkPalette()
      Dim i As Integer, iRed As Integer, iGreen As Integer, iBlue As Integer
      Dim lcolor As Long
      For i = 1 To 56
        lcolor = ActiveWorkbook.Colors(i)
        iRed = lcolor Mod &H100  'get red component
        lcolor = lcolor \ &H100  'divide
        iGreen = lcolor Mod &H100 'get green component
        lcolor = lcolor \ &H100  'divide
        iBlue = lcolor Mod &H100 'get blue component
        Debug.Print "Palette " & i & ": R=" & iRed & " B=" & iBlue & " G=" & iGreen
      Next i
    End Sub

This will let you set the palette

Public Sub setPalette(palIdx As Integer, r As Integer, g As Integer, b As Integer)
  ActiveWorkbook.Colors(palIdx) = RGB(r, g, b)
End Sub

Answer 2

Thank you for the answers and the comments as well.

It really gave me great trouble because my client had other plugins installed into Excel which also tampered with the color palette.

I ended up replacing a few colors in the palette an then asigning my elements the specific ColorIndex, but boy, it's not pretty.