在 VBA 中设置字体颜色

Setting a font color in VBA

提问by Peter Lindholm

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

我想将单元格的字体颜色设置为特定的 RGB 值。

If I use

如果我使用

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

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

我确实变黄了，但如果我使用更奇特的 RGB 值，例如：

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?

为什么我不能只使用任何 RGB 值？你知道任何解决方法吗？

Thanks.

谢谢。

回答by LeppyR64

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.

Excel 仅使用调色板中的颜色。当您使用 RGB 值设置单元格时，它会选择调色板中最匹配的单元格。您可以使用您的颜色更新调色板，然后选择您的颜色，这将起作用。

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

回答by Nigel Heffernan

A quick tip: the Excel Palette has two rows of colours which are rarely used and can usually be set to custom values without visible changes to other peoples' sheets.

快速提示：Excel 调色板有两行很少使用的颜色，通常可以设置为自定义值，而不会对其他人的工作表进行可见的更改。

Here's the code to create a reasonable set of 'soft-tone' colours which are far less offensive than the defaults:

这是创建一组合理的“柔和色调”颜色的代码，这些颜色远没有默认值那么令人反感：

Public Sub SetPalePalette(Optional wbk As Excel.Workbook)
' This subroutine creates a custom palette of pale tones which you can use for controls, headings and dialogues
'

' **      THIS CODE IS IN THE PUBLIC DOMAIN   **
' Nigel Heffernan   http://Excellerando.Blogspot.com

' The Excel color palette has two hidden rows which are rarely used:
' Row 1: colors 17 to 24
' Row 2: colors 25 to 32   - USED BY SetGrayPalette in this workbook
'

' Code to capture existing Screen Updating settting and, if necessary,
' temporarily suspend updating while this procedure generates irritating
' flickers onscreen... and restore screen updating on exit if required.

Dim bScreenUpdating As Boolean

bScreenUpdating = Application.ScreenUpdating

If bScreenUpdating = True Then
    Application.ScreenUpdating = False
End If

'If Application.ScreenUpdating <> bScreenUpdating Then
'    Application.ScreenUpdating = bScreenUpdating
'End If

If wbk Is Nothing Then
    Set wbk = ThisWorkbook
End If

With wbk

.Colors(17) = &HFFFFD0  ' pale cyan
.Colors(18) = &HD8FFD8  ' pale green.
.Colors(19) = &HD0FFFF  ' pale yellow
.Colors(20) = &HC8E8FF  ' pale orange
.Colors(21) = &HDBDBFF  ' pale pink
.Colors(22) = &HFFE0FF  ' pale magenta
.Colors(23) = &HFFE8E8  ' lavender
.Colors(24) = &HFFF0F0  ' paler lavender


End With
If Application.ScreenUpdating <> bScreenUpdating Then
    Application.ScreenUpdating = bScreenUpdating
End If
End Sub
Public Sub SetGreyPalette()
' This subroutine creates a custom palette of greyshades which you can use for controls, headings and dialogues
' **      THIS CODE IS IN THE PUBLIC DOMAIN   **' Nigel Heffernan   http://Excellerando.Blogspot.com
' The Excel color palette has two hidden rows which are rarely used:
' Row 1: colors 17 to 24 ' - USED BY SetPalePalette in this workbook
' Row 2: colors 25 to 32
' Code to capture existing Screen Updating settting and, if necessary,
' temporarily suspend updating while this procedure generates irritating
' flickers onscreen... remember to restore screen updating on exit!
Dim bScreenUpdating As Boolean
bScreenUpdating = Application.ScreenUpdating
If bScreenUpdating = True Then
    Application.ScreenUpdating = False
End If
'If Application.ScreenUpdating <> bScreenUpdating Then
'    Application.ScreenUpdating = bScreenUpdating
'End If
With ThisWorkbook
    .Colors(25) = &HF0F0F0
    .Colors(26) = &HE8E8E8
    .Colors(27) = &HE0E0E0
    .Colors(28) = &HD8D8D8
    .Colors(29) = &HD0D0D0
    .Colors(30) = &HC8C8C8
    '             &HC0C0C0 ' Skipped &HC0C0C0 - this is the regular 25% grey in the main palette
    .Colors(31) = &HB8B8B8 ' Note that the gaps are getting wider: the human eye is more sensitive
    .Colors(32) = &HA8A8A8 ' to changes in light greys, so this will be perceived as a linear scale
End With
'The right-hand column of the Excel default palette specifies the following greys:
'   Colors(56) = &H333333
'   Colors(16) = &H808080
'   Colors(48) = &H969696
'   Colors(15) = &HC0C0C0   ' the default '25% grey'
' This should be modified to improve the color 'gap' and make the colours easily-distinguishable:
With ThisWorkbook
    .Colors(56) = &H505050
    .Colors(16) = &H707070
    .Colors(48) = &H989898
  ' .Colors(15) = &HC0C0C0
End With
If Application.ScreenUpdating <> bScreenUpdating Then
    Application.ScreenUpdating = bScreenUpdating
End If
End Sub

You may choose to write a 'CaptureColors' and 'ReinstateColors' function for each workbook's Open() and BeforeClose() events... Or even for each worksheet's activate and deactivate event.

您可以选择为每个工作簿的 Open() 和 BeforeClose() 事件编写“CaptureColors”和“ReinstateColors”函数……甚至为每个工作表的激活和停用事件编写。

I have code lying around somewhere that creates a 'thermal' colour gradient for 3-D charts, giving you a progression from 'Cold' blue to 'Hot' reds in thirty-two steps. This is harder than you might think: a gradient of colors that will be perceived as 'equal intervals' by the human visual system (which runs on a logarithmic scale of intensity and has nonlinear weightings for red, green and blue as 'strong' colours) takes time to construct - and you have to use VBA to coerce MS Chart into using the colours you specify, in the order you specified.

我在某处放置了一些代码，可以为 3D 图表创建“热”颜色渐变，让您分 32 个步骤从“冷”蓝色到“热”红色。这比您想象的要困难：人类视觉系统将感知为“等间隔”的颜色渐变（它在强度的对数刻度上运行，并且将红色、绿色和蓝色作为“强”颜色具有非线性权重) 需要时间来构建 - 您必须使用 VBA 强制 MS Chart 按照您指定的顺序使用您指定的颜色。

回答by Marko

Sub color()

bj = CStr(Hex(ActiveCell.Interior.Color))
If Len(bj) < 6 Then
    Do Until Len(bj) = 6
        bj = "0" & bj
    Loop
End If

R = CLng("&H" & Right(bj, 2))
bj = Left(bj, Len(bj) - 2)
G = CLng("&H" & Right(bj, 2))
bj = Left(bj, Len(bj) - 2)
B = CLng("&H" & bj)

End Sub

回答by Peter Lindholm

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.

这真的给我带来了很大的麻烦，因为我的客户在 Excel 中安装了其他插件，这些插件也篡改了调色板。

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

我最终替换了调色板中的一些颜色，然后为我的元素分配了特定的 ColorIndex，但是男孩，它并不漂亮。