vba 如何使用单元格内的十六进制颜色值突出显示单元格？

How to highlight a cell using the hex color value within the cell?

提问by knowbody

I have a spreadsheet of symbols and matching hex colors. I want to fill the cell itself (or the one next to it) with the hex color within the cell. I've read a bit about "conditional formatting", and I think that's the way to do it.

我有一个符号和匹配的十六进制颜色的电子表格。我想用单元格内的十六进制颜色填充单元格本身（或旁边的单元格）。我已经阅读了一些关于“条件格式”的内容，我认为这就是这样做的方法。

How might I achieve the result I would like?

我怎样才能达到我想要的结果？

回答by pnuts

Can't be achieved with Conditional Formatting for all colours.

所有颜色的条件格式都无法实现。

Assuming: Row1 contains Data Labels, data set does not have gaps, the HEX colour is for the fill not the font, you have parsed the HEX colour values (numbers, not formulae) into Columns C:E (R,G,B) and that you do not require to do this often, then the ColourCells macro might suit:

假设：Row1 包含数据标签，数据集没有间隙，HEX 颜色用于填充而不是字体，您已将 HEX 颜色值（数字，而不是公式）解析为列 C:E (R,G,B)并且您不需要经常这样做，那么 ColourCells 宏可能适合：

Sub ColourCells()
Dim HowMany As Integer
On Error Resume Next
Application.DisplayAlerts = False
HowMany = Application.InputBox _
(Prompt:="Enter last row number.", Title:="To apply to how many rows?", Type:=1)
On Error GoTo 0
Application.DisplayAlerts = True
If HowMany = 0 Then
Exit Sub
Else
   Dim i As Integer
   For i = 2 To HowMany
      Cells(i, 3).Interior.Color = RGB(Cells(i, 3), Cells(i, 4), Cells(i, 5))
   Next i
End If
End Sub

and enter the value you want for n when prompted.

并在出现提示时输入您想要的 n 值。

Sample output and formulae etc:

示例输出和公式等：

Excel's RGB() function actually creates a BGR value (I don't think anybody that might know why is saying why though) so Excel shows nibbles in reverse order. For the code Columns3,4,5 was logical but BGR rather than the conventional RGB in the image I thought might look odd. For F in the image the C3 value (the LEFThand column of the 'RGB' three) is derived from applying RIGHT()to the Hex colour.

Excel 的 RGB() 函数实际上创建了一个 BGR 值（我认为没有人可能知道为什么会说为什么）所以 Excel 以相反的顺序显示半字节。对于代码 Columns3,4,5 是合乎逻辑的，但我认为图像中的 BGR 而不是传统的 RGB 可能看起来很奇怪。对于F上的图像中的C3值（LEFT的“RGB”三列）从施加衍生RIGHT（）到十六进制的颜色。

回答by user3646932

Minor edit to Jon Peltier's answer. His function ALMOST works, but the colors it renders are incorrect due to the fact the Excel will render as BGR rather than RGB. Here is the corrected function, which swaps the pairs of Hex values into the 'correct' order:

对乔恩·珀尔帖 (Jon Peltier) 的回答进行了小幅编辑。他的函数几乎可以工作，但它呈现的颜色不正确，因为 Excel 将呈现为 BGR 而不是 RGB。这是更正后的函数，它将成对的十六进制值交换为“正确”的顺序：

Sub ColorCellsByHex()
  Dim rSelection As Range, rCell As Range, tHex As String

  If TypeName(Selection) = "Range" Then
  Set rSelection = Selection
    For Each rCell In rSelection
      tHex = Mid(rCell.Text, 6, 2) & Mid(rCell.Text, 4, 2) & Mid(rCell.Text, 2, 2)
      rCell.Interior.Color = WorksheetFunction.Hex2Dec(tHex)
    Next
  End If
End Sub

回答by Jon Peltier

Much simpler:

更简单：

ActiveCell.Interior.Color = WorksheetFunction.Hex2Dec(Mid$(ActiveCell.Text, 2))

Mid strips off the leading "#", Hex2Dec turns the hex number into a decimal value that VBA can use.

Mid 去掉前导的“#”，Hex2Dec 将十六进制数转换为 VBA 可以使用的十进制值。

So select the range to process, and run this:

所以选择要处理的范围，然后运行：

Sub ColorCellsByHexInCells()
  Dim rSelection As Range, rCell As Range

  If TypeName(Selection) = "Range" Then
  Set rSelection = Selection
    For Each rCell In rSelection
      rCell.Interior.Color = WorksheetFunction.Hex2Dec(Mid$(rCell.Text, 2))
    Next
  End If
End Sub

回答by kadrleyn

For this, a userform can be made with the Hex2Dec function.

为此，可以使用 Hex2Dec 函数制作用户表单。

Function Hex2Dec(n1 As String) As Long
    Dim nl1 As Long
    Dim nGVal As Long
    Dim nSteper As Long
    Dim nCount As Long
    Dim x As Long
    Dim nVal As Long
    Dim Stepit As Long
    Dim hVal As String

    nl1 = Len(n1)
    nGVal = 0
    nSteper = 16
    nCount = 1
    For x = nl1 To 1 Step -1
       hVal = UCase(Mid$(n1, x, 1))
       Select Case hVal
         Case "A"
           nVal = 10
         Case "B"
           nVal = 11
         Case "C"
           nVal = 12
         Case "D"
           nVal = 13
         Case "E"
           nVal = 14
         Case "F"
           nVal = 15
         Case Else
           nVal = Val(hVal)
       End Select
       Stepit = (nSteper ^ (nCount - 1))
       nGVal = nGVal + nVal * Stepit
       nCount = nCount + 1
    Next x
    Hex2Dec = nGVal
End Function
...
UserForm1.TextBox1 = "RGB(" & Hex2Dec(UserForm1.txtHex1.Value) & "," & _
           Hex2Dec(UserForm1.txtHex2.Value) & "," & Hex2Dec(UserForm1.txtHex3.Value) & ")"

For example ;the entered value to textbox: #FF8800 - Result : RGB(255,136,0)

例如;输入到文本框的值：#FF8800 - 结果：RGB(255,136,0)

回答by Rich Morey

This is another option - it updates the cell color when you select the cell assuming the value in the cell starts with "#" and is 7 characters.

这是另一个选项 - 当您选择单元格时，它会更新单元格颜色，假设单元格中的值以“#”开头且为 7 个字符。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If (Left(ActiveCell.Text, 1) = "#" And Len(ActiveCell.Text) = 7) Then
    ActiveCell.Interior.Color = WorksheetFunction.Hex2Dec(Mid$(ActiveCell.Text, 2))
End If

End Sub

回答by Excel Hero

There is no need to repeatedly pierce the VBA/Worksheet barrier to convert. This streamlined version gets the byte order correct:

无需反复穿透 VBA/Worksheet 屏障进行转换。这个精简版的字节顺序是正确的：

Sub ColorCellsByHex()
    Dim r
    If TypeName(Selection) <> "Range" Then Exit Sub
    For Each r In Selection
        r.Interior.Color = Abs(("&H" & Mid(r, 6, 2) & Mid(r, 4, 2) & Mid(r, 2, 2)))
    Next
End Sub