基于单元格颜色生成服务名称或反向设置单元格颜色的方法咨询

一、根据单元格颜色代码自动生成服务名称列表 #

Excel内置公式无法直接读取单元格颜色代码，GET.CELL是宏表函数，需通过定义名称调用，具体步骤如下：

1. 创建颜色读取名称

   • 按Ctrl+F3打开「名称管理器」，点击「新建」
   • 名称设为GetColor_Q，引用位置输入：=GET.CELL(38,Sheet1!Q3)（替换Sheet1为你的工作表名，Q3为目标单元格）
   • 重复操作，分别创建GetColor_R（引用GET.CELL(38,Sheet1!R3)）和GetColor_S（引用GET.CELL(38,Sheet1!S3)）

2. 在Z3生成服务列表
   用TEXTJOIN拼接符合条件的服务名，公式如下：

   =TEXTJOIN(", ",TRUE,IF(GetColor_Q=20,"Family Medicine(FM)",""),IF(GetColor_R=40,"Behavioral Health(BH)",""),IF(GetColor_S=24,"Chiropractic(Chiro)",""))
   

   Excel 365/2021直接回车即可，旧版本需按Ctrl+Shift+Enter作为数组公式输入。公式会自动过滤空值，只显示对应颜色的服务名称。

二、输入服务名称后自动设置对应单元格颜色 #

此需求无法用公式实现，需用VBA代码触发格式修改：

1. 打开VBA编辑器：按Alt+F11，找到目标工作表（如Sheet1）并双击打开代码窗口

2. 粘贴以下代码：

   Private Sub Worksheet_Change(ByVal Target As Range)
       ' 仅监听Z列单元格变化
       If Target.Column <> 26 Or Target.Cells.Count > 1 Then Exit Sub
       
       ' 定义服务-颜色-单元格映射
       Dim serviceMap As Variant
       serviceMap = Array( _
           Array("Family Medicine(FM)", 20, Range("Q3")), _
           Array("Behavioral Health(BH)", 40, Range("R3")), _
           Array("Chiropractic(Chiro)", 24, Range("S3")) _
       )
       
       ' 先清空目标区域颜色
       Range("Q3:S3").Interior.ColorIndex = xlColorIndexNone
       
       ' 拆分输入的服务名称（支持逗号分隔）
       Dim inputArr As Variant
       inputArr = Split(Target.Value, ",")
       
       ' 匹配服务并设置颜色
       Dim i As Integer, j As Integer
       For i = LBound(inputArr) To UBound(inputArr)
           Dim currService As String
           currService = Trim(inputArr(i))
           
           For j = LBound(serviceMap) To UBound(serviceMap)
               If currService = serviceMap(j)(0) Then
                   serviceMap(j)(2).Interior.ColorIndex = serviceMap(j)(1)
                   Exit For
               End If
           Next j
       Next i
   End Sub
   

   代码逻辑：Z列内容变化时，先清空Q3-S3的颜色，再根据输入的服务名（支持多个服务逗号分隔），匹配对应颜色代码和单元格，自动设置底色。

3. 保存文件为「Excel启用宏的工作簿（.xlsm）」，否则代码无法生效

注意事项 #

• GET.CELL依赖工作表计算，手动修改单元格颜色后需按F9刷新结果（或设置工作表自动计算）
• VBA代码需启用宏，确保Excel信任宏运行

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