如何无需条件格式让Excel单元格背景匹配对应城市的十六进制颜色

针对你需要制作按小时分段的旅行行程可视化表，实现单元格背景匹配城市指定颜色、旅行中时段为白色，且无需频繁维护条件格式的需求，提供以下两种方案：

一、VBA自动配色方案（推荐，高效易维护） #

该方案通过VBA读取行程数据源和城市颜色表，自动遍历可视化表的每个单元格并设置背景色，新增/修改城市或行程后只需运行宏即可完成更新，无需手动调整格式规则。

1. 准备工作 #

• 将行程数据整理为Excel表格（选中数据→插入→表格），命名为TripTable（在表格设计选项卡修改表格名称）。
• 将城市与颜色列表整理为Excel表格，命名为CityColorTable。
• 可视化表结构：A列为日期（格式与行程表一致），第一行为小时段（如00:00到23:00，格式设为时间）。

2. VBA代码实现 #

按Alt+F11打开VBA编辑器，右键点击工作簿→插入→模块，粘贴以下代码：

Sub UpdateTripScheduleColors()
    Dim wsVisual As Worksheet
    Dim tblTrip As ListObject, tblColor As ListObject
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim currentDate As Date, currentTime As Date
    Dim isTraveling As Boolean
    Dim currentCity As String
    Dim colorHex As String
    
    ' 替换为实际工作表名称
    Set wsVisual = ThisWorkbook.Worksheets("行程可视化")
    Set tblTrip = ThisWorkbook.Worksheets("行程数据").ListObjects("TripTable")
    Set tblColor = ThisWorkbook.Worksheets("城市颜色").ListObjects("CityColorTable")
    
    ' 清除现有背景色
    wsVisual.UsedRange.Interior.ColorIndex = xlColorIndexNone
    
    ' 获取可视化表的行列范围
    lastRow = wsVisual.Cells(wsVisual.Rows.Count, "A").End(xlUp).Row
    lastCol = wsVisual.Cells(1, wsVisual.Columns.Count).End(xlToLeft).Column
    
    ' 遍历每个单元格
    For i = 2 To lastRow
        currentDate = wsVisual.Cells(i, "A").Value
        
        For j = 2 To lastCol
            currentTime = wsVisual.Cells(1, j).Value
            isTraveling = False
            currentCity = ""
            
            ' 判断是否处于旅行中
            On Error Resume Next
            isTraveling = Not IsError(Application.Match(True, _
                (tblTrip.ListColumns("日期").DataBodyRange.Value = currentDate) _
                * (tblTrip.ListColumns("出发时间").DataBodyRange.Value <= currentTime) _
                * (tblTrip.ListColumns("到达时间").DataBodyRange.Value > currentTime), 0))
            On Error GoTo 0
            
            If isTraveling Then
                ' 旅行中设置白色背景
                wsVisual.Cells(i, j).Interior.Color = vbWhite
            Else
                ' 获取当前所在城市
                currentCity = GetCurrentCity(currentDate, currentTime, tblTrip)
                If currentCity <> "" Then
                    ' 查找对应十六进制颜色
                    colorHex = tblColor.ListColumns("颜色").DataBodyRange.Find( _
                                What:=currentCity, LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1).Value
                    ' 转换颜色并设置背景
                    wsVisual.Cells(i, j).Interior.Color = HexToRGB(colorHex)
                End If
            End If
        Next j
    Next i
    
    MsgBox "行程配色已更新完成！", vbInformation
End Sub

' 辅助函数：获取指定日期和时间的当前所在城市
Function GetCurrentCity(targetDate As Date, targetTime As Date, tblTrip As ListObject) As String
    Dim arrTrip As Variant
    Dim k As Long
    Dim lastArriveTime As Date
    Dim lastCity As String
    
    arrTrip = tblTrip.DataBodyRange.Value
    lastArriveTime = 0
    lastCity = ""
    
    ' 查找当天最晚到达的城市
    For k = LBound(arrTrip, 1) To UBound(arrTrip, 1)
        If arrTrip(k, 1) = targetDate Then
            If arrTrip(k, 5) <= targetTime And arrTrip(k, 5) > lastArriveTime Then
                lastArriveTime = arrTrip(k, 5)
                lastCity = arrTrip(k, 4)
            End If
        End If
    Next k
    
    ' 若时间早于当天第一个到达时间，取最早的出发城市
    If lastArriveTime = 0 Then
        For k = LBound(arrTrip, 1) To UBound(arrTrip, 1)
            If arrTrip(k, 1) = targetDate Then
                lastCity = arrTrip(k, 2)
                Exit For
            End If
        Next k
    End If
    
    GetCurrentCity = lastCity
End Function

' 辅助函数：十六进制颜色转RGB值
Function HexToRGB(hexStr As String) As Long
    Dim r As Integer, g As Integer, b As Integer
    
    ' 去除#号
    hexStr = Replace(hexStr, "#", "")
    
    r = Val("&H" & Left(hexStr, 2))
    g = Val("&H" & Mid(hexStr, 3, 2))
    b = Val("&H" & Right(hexStr, 2))
    
    HexToRGB = RGB(r, g, b)
End Function

3. 使用步骤 #

1. 修改代码中工作表名称（"行程可视化"、"行程数据"、"城市颜色"）为你的实际工作表名称。
2. 每次更新行程数据或城市颜色后，按Alt+F8，选择UpdateTripScheduleColors运行宏，即可自动更新所有单元格背景色。
3. 可选：将宏添加到快速访问工具栏，方便维护人员一键操作。

方案优势 #

• 无需维护复杂的条件格式规则，新增/修改城市仅需更新城市颜色表。
• 行程变更后一键更新，适配所有时段的颜色设置。
• 代码逻辑可灵活调整（如修改“旅行中”的颜色、调整城市判断逻辑）。

二、无VBA公式驱动方案（依赖辅助列） #

若无法启用VBA，可通过辅助列+名称管理器+条件格式实现，但操作相对繁琐：

1. 预处理城市颜色表 #

在城市颜色表新增辅助列，输入公式将十六进制颜色转为RGB值：

=RGB(HEX2DEC(LEFT(B2,2)),HEX2DEC(MID(B2,3,2)),HEX2DEC(RIGHT(B2,2)))

选中城市列和RGB列，定义名称CityColorMap（公式：=Sheet2!$A$2:$C$6，根据实际行数调整）。

2. 可视化表添加状态判断辅助列 #

在可视化表右侧新增辅助列，每个单元格输入公式判断当前时段的状态（“旅行中”或城市名称）：

=LET(
    currentDate,A2,
    currentTime,B1,
    tripMatch,COUNTIFS(TripTable[日期],currentDate,TripTable[出发时间],"<="&currentTime,TripTable[到达时间],">"&currentTime),
    IF(tripMatch>0,"旅行中",
        XLOOKUP(TRUE,TripTable[日期]=currentDate,TripTable[到达时间],"",0,-1)
        |> LAMBDA(lastArriveTime,IF(currentTime>=lastArriveTime,XLOOKUP(TRUE,TripTable[日期]=currentDate,TripTable[到达城市],"",0,-1),XLOOKUP(TRUE,TripTable[日期]=currentDate,TripTable[出发城市],"",0,-1)))
    )
)

3. 设置条件格式规则 #

选中可视化表数据区域，添加两条条件格式规则：

• 规则1：公式=$B2="旅行中"，设置背景色为白色。
• 规则2：公式=$B2<>""，通过名称管理器关联城市RGB值设置背景色（需使用GET.CELL宏表函数，需启用Excel的宏功能）。

注：无VBA方案需手动维护辅助列，且颜色设置依赖宏表函数，灵活性不如VBA方案。

内容的提问来源于stack exchange，提问作者Gary R.