如何基于单元格HEX/RGB值动态修改Excel条件格式自定义颜色

Excel原生条件格式的颜色设置不支持直接引用单元格中的HEX值，必须借助VBA来实现颜色的动态更新。以下是具体的解决方案：

1. 编写HEX转RGB的VBA转换函数 #

打开VBA编辑器（按Alt+F11），右键点击目标工作表所在的工作簿 → 插入 → 模块，粘贴以下代码：

Function HexToRGB(hexStr As String) As Long
    ' 兼容带#和不带#的HEX格式
    If Left(hexStr, 1) = "#" Then hexStr = Mid(hexStr, 2)
    ' 将HEX字符串转换为Excel可识别的RGB颜色值
    HexToRGB = RGB( _
        CLng("&H" & Mid(hexStr, 1, 2)), _
        CLng("&H" & Mid(hexStr, 3, 2)), _
        CLng("&H" & Mid(hexStr, 5, 2)) _
    )
End Function

2. 编写工作表变更事件宏 #

在VBA编辑器左侧的项目窗口中，双击目标工作表（比如Sheet1，即你的数据所在工作表），在右侧代码窗口粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim colorHexRanges As Range
    ' 定义需要监控的HEX颜色单元格区域
    Set colorHexRanges = Union(Me.Range("G16:G18"), Me.Range("L16:L18"))
    
    ' 仅当修改的是HEX颜色区域时触发更新
    If Not Intersect(Target, colorHexRanges) Is Nothing Then
        UpdateConditionalFormatColors
    End If
End Sub

Sub UpdateConditionalFormatColors()
    Dim ws As Worksheet
    Set ws = Me
    
    ' --- 更新Score #1（B3:B17）的条件格式颜色 ---
    ' 注意：这里假设红、黄、绿规则是条件格式列表中的第1、2、3条，需根据你的实际规则顺序调整
    With ws.Range("B3:B17").FormatConditions(1) ' 红组规则
        .Interior.Color = HexToRGB(ws.Range("G16").Value) ' 背景色对应G16
        .Font.Color = HexToRGB(ws.Range("L16").Value) ' 字体色对应L16
    End With
    With ws.Range("B3:B17").FormatConditions(2) ' 黄组规则
        .Interior.Color = HexToRGB(ws.Range("G17").Value)
        .Font.Color = HexToRGB(ws.Range("L17").Value)
    End With
    With ws.Range("B3:B17").FormatConditions(3) ' 绿组规则
        .Interior.Color = HexToRGB(ws.Range("G18").Value)
        .Font.Color = HexToRGB(ws.Range("L18").Value)
    End With
    
    ' --- 更新Score #2（D3:D17）的条件格式颜色 ---
    With ws.Range("D3:D17").FormatConditions(1) ' 红组规则
        .Interior.Color = HexToRGB(ws.Range("G16").Value) ' 若Score#2需独立颜色，替换为对应单元格（如M16）
        .Font.Color = HexToRGB(ws.Range("L16").Value)
    End With
    With ws.Range("D3:D17").FormatConditions(2) ' 黄组规则
        .Interior.Color = HexToRGB(ws.Range("G17").Value)
        .Font.Color = HexToRGB(ws.Range("L17").Value)
    End With
    With ws.Range("D3:D17").FormatConditions(3) ' 绿组规则
        .Interior.Color = HexToRGB(ws.Range("G18").Value)
        .Font.Color = HexToRGB(ws.Range("L18").Value)
    End With
End Sub

关键注意事项 #

• 条件格式顺序匹配：代码中默认红、黄、绿规则是条件格式列表的第1、2、3条，如果你实际设置的规则顺序不同，需修改FormatConditions(n)中的n值。
• HEX格式兼容：支持输入带#或不带#的HEX值（例如#FF0000或FF0000均有效）。
• 独立颜色设置：如果Score #2需要使用独立的颜色组，只需将代码中Score #2部分的单元格引用（如G16）替换为对应的颜色HEX单元格（例如M16）。
• 宏启用要求：保存文件时需选择.xlsm格式，打开文件时需启用宏才能触发颜色更新。

内容的提问来源于stack exchange，提问作者vivekmalhotra