Excel单列多颜色条件格式跨表同步问题求助

背景 #

• Sheet2（发货请求列表）B列（从B2开始）通过公式 ='Master List'!D2 同步Sheet1（主列表）D列数据，共700+行且持续新增
• Sheet1的D列为下拉列表，仅当值为"Open"时，会根据V列日期应用以下条件格式：
  • =AND($V2<TODAY(),$D2="open") → 填充紫色
  • =AND($V2-TODAY()>=0, $V2-TODAY()<=2,$D2="open") → 填充红色
  • =AND($V2-TODAY()>=3, $V2-TODAY()<=4,$D2="open") → 填充橙色
  • =AND($V2-TODAY()>=5, $V2-TODAY()<=7,$D2="open") → 填充黄色

已尝试但无效的方法 #

• 方法1：直接在Sheet2条件格式中引用Sheet1单元格，颜色显示错误
• 方法2：使用INDIRECT函数设置Sheet2条件格式，操作存疑，未解决问题
• 方法3：通过VBA自定义函数FindColor提取Sheet1 D列ColorIndex到Sheet2 C列，结果不一致

  Function FindColor(n As Range) As Integer
  FindColor = n.Interior.ColorIndex
  End Function
  

• 方法4：在Sheet1新增C列用公式生成颜色名称，公式存在错误（误将$D662写为$V662）且逻辑不完整

  =IF(AND($V662<TODAY(),$D662="open"),"purple",IF(AND($V662-TODAY()>=0,$V662-TODAY()<=2,$V662="open"),"red",""))
  

可行解决方案 #

方案1：直接在Sheet2中引用Sheet1的判定条件 #

选中Sheet2的B列目标区域（如B2:B700），依次添加条件格式规则：

1. 紫色规则：公式为 =AND('Master List'!$V2<TODAY(),'Master List'!$D2="open")，设置填充色为紫色
2. 红色规则：公式为 =AND('Master List'!$V2-TODAY()>=0, 'Master List'!$V2-TODAY()<=2,'Master List'!$D2="open")，设置填充色为红色
3. 橙色规则：公式为 =AND('Master List'!$V2-TODAY()>=3, 'Master List'!$V2-TODAY()<=4,'Master List'!$D2="open")，设置填充色为橙色
4. 黄色规则：公式为 =AND('Master List'!$V2-TODAY()>=5, 'Master List'!$V2-TODAY()<=7,'Master List'!$D2="open")，设置填充色为黄色

注意：公式中行号使用相对引用（如V2而非$V$2），确保规则自动适配每一行对应的Sheet1单元格。

方案2：通过辅助列同步颜色标识后设置格式 #

1. 在Sheet1新增辅助列（如C列），修正并补全颜色判定公式：

   =IF(AND($V2<TODAY(),$D2="open"),"purple",
      IF(AND($V2-TODAY()>=0,$V2-TODAY()<=2,$D2="open"),"red",
         IF(AND($V2-TODAY()>=3,$V2-TODAY()<=4,$D2="open"),"orange",
            IF(AND($V2-TODAY()>=5,$V2-TODAY()<=7,$D2="open"),"yellow",""))))
   

2. 在Sheet2的C列用公式 ='Master List'!C2 同步该颜色标识
3. 给Sheet2的B列设置条件格式：
   • 规则1：单元格值等于"purple" → 紫色填充
   • 规则2：单元格值等于"red" → 红色填充
   • 规则3：单元格值等于"orange" → 橙色填充
   • 规则4：单元格值等于"yellow" → 黄色填充

方法3失效原因说明 #

条件格式触发的颜色属于规则应用效果，并非单元格本身的默认填充色，自定义函数FindColor只能读取单元格的基础Interior.ColorIndex，无法获取条件格式生效后的动态颜色。

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