Excel 365下拉列表如何同步源列表的文本与背景色？

因为条件格式无法动态适配Lists工作表中新增条目、修改文本或背景色的操作，所以用VBA是更可靠的方案，以下是具体步骤：

一、用VBA实现实时同步 #

1. 打开VBA编辑器 #

按 Alt + F11 快速打开VBA编辑器。

2. 给Detail工作表添加选中事件代码 #

在左侧项目窗口双击Detail工作表，右侧代码窗口粘贴以下代码：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Dim listRng As Range
    
    ' 定位Lists里的责任人列表（自动取N/A上方的所有非空行）
    Set listRng = Sheets("Lists").Range("A1", Sheets("Lists").Range("A" & Rows.Count).End(xlUp).Offset(-1))
    
    ' 只处理单个带下拉列表的单元格
    If Target.Count = 1 And Target.Validation.Type = xlValidateList Then
        ' 在列表中查找当前选中的值
        Set rng = listRng.Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
        ' 找到匹配项就同步背景色
        If Not rng Is Nothing Then
            Target.Interior.Color = rng.Interior.Color
        End If
    End If
End Sub

3. 给Lists工作表添加修改同步代码 #

在左侧项目窗口双击Lists工作表，右侧代码窗口粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    Dim listRng As Range
    
    ' 定位Lists里的责任人列表
    Set listRng = Sheets("Lists").Range("A1", Sheets("Lists").Range("A" & Rows.Count).End(xlUp).Offset(-1))
    
    ' 只处理列表范围内的修改
    If Not Intersect(Target, listRng) Is Nothing Then
        ' 遍历Detail表中所有带下拉的单元格，更新匹配项的背景色
        For Each cell In Sheets("Detail").UsedRange
            If cell.Validation.Type = xlValidateList And cell.Value = Target.Value Then
                cell.Interior.Color = Target.Interior.Color
            End If
        Next cell
    End If
End Sub

二、代码说明 #

• SelectionChange事件：用户在Detail表选下拉条目时，自动去Lists表找对应值的单元格，同步背景色。
• Change事件：用户修改Lists表中条目的文本或背景色时，自动更新Detail表所有匹配该条目的单元格颜色。
• 列表范围会自动适配新增行的情况，不用手动调整。
• 如果Detail表的下拉只在特定区域（比如B2:Z100），可以把Sheets("Detail").UsedRange改成Sheets("Detail").Range("B2:Z100")，加快运行速度。

三、注意事项 #

1. 保存工作簿时要选**启用宏的工作簿（.xlsm）**格式，不然宏会失效。
2. 打开工作簿时要启用宏才能生效。
3. 如果N/A不在Lists表的A列最后一行，需要调整代码里的Offset(-1)参数，确保取到正确的列表范围。

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