Setting a font color in VBA

Accepted answer +7 upvotes

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