我有一个符号和匹配的十六进制颜色的电子表格。我希望用单元格内的十六进制颜色填充单元格本身(或旁边的单元格)。我读了一些关于"条件格式化"的内容,我认为这是实现目标的方法。
我应该如何实现我想要的结果?
我有一个符号和匹配的十六进制颜色的电子表格。我希望用单元格内的十六进制颜色填充单元格本身(或旁边的单元格)。我读了一些关于"条件格式化"的内容,我认为这是实现目标的方法。
我应该如何实现我想要的结果?
无法使用条件格式实现所有颜色。
假设:行1包含数据标签,数据集不具有间隙,HEX颜色用于填充而非字体,您已将HEX颜色值(数字而非公式)解析为C:E列(R、G、B),并且您不需要经常执行此操作,则可以使用ColorCells宏:
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
并且在提示输入n值时输入您想要的值。
示例输出和公式等:
Excel的RGB()函数实际上创建了一个BGR值(我不认为任何可能知道原因的人都会说出原因),因此Excel以相反的顺序显示半字节。 对于代码Columns3,4,5是合乎逻辑的,但图片中使用的是BGR而不是常规的RGB,我认为这可能看起来有点奇怪。对于图像中的F,C3值('RGB'三个颜色的左手列)是通过将RIGHT()应用于十六进制颜色来导出的。
对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
rCell.Font.Color = WorksheetFunction.Hex2Dec(tHex)
- Stefan de KokActiveCell.Interior.Color = WorksheetFunction.Hex2Dec(Mid$(ActiveCell.Text, 2))
Mid去掉了前导的“#”,Hex2Dec将十六进制数转换为VBA可用的十进制值。
因此,选择要处理的范围,并运行以下代码:
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
无需反复穿越VBA/工作表障碍进行转换。这个简化版本可以正确获取字节顺序:
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
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
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) & ")"
Range("A1:A6").Interior.Color = RGB(200,160,35)
。这意味着你还需要进行数学计算将其转换为RGB或利用Excel奇怪的十六进制方法。 - Marc