我遇到过好几个类似的Excel条件格式失效的案例，你的问题核心其实集中在动态范围引用隐患、旧规则堆积和触发时机缺失这几个点上，下面一步步帮你拆解解决：

一、问题根源拆解 #

1. 动态范围的字符串拼接坑：你用rngNew = "D1" & ":" & Cells(lRowEnd, 4).Address这种方式拼接地址，如果lRowEnd没有在每次执行时重新计算（比如文件重开后没更新这个值），范围就会出错；另外Cells(lRowEnd,3).Address(False,True)生成的混合引用，在条件格式公式里容易出现意外偏移。
2. 未清理旧条件格式：每次执行代码都新增规则，时间长了文件里会堆积一堆无效规则，Excel重开后可能优先加载这些失效的旧规则。
3. 公式引用方式错误：你拼接的公式是整列引用（比如=C1:CXX="ShopFree"），但条件格式的公式应该是针对单个单元格的相对引用——Excel会自动把单单元格公式应用到整个范围，整列引用在文件重开后容易因为范围解析失败失效。
4. 触发时机不全：你的代码只在修改列、增删工作表或加载时执行，但文件重新打开的Workbook_Open事件没覆盖到，导致重开后不会重新生成正确规则。

二、快速排查步骤 #

• 检查lRowEnd计算逻辑：确认lRowEnd是不是用lRowEnd = Cells(Rows.Count, 3).End(xlUp).Row获取C列最后一行？如果这个计算只在某个单次事件里执行，重开后就会用旧值。
• 查看条件格式规则：打开「开始」→「条件格式」→「管理规则」，看看是不是有一堆重复规则，且公式里出现#REF!之类的无效引用。
• 手动测试规则有效性：手动创建一个条件格式，公式用=$C1="ShopFree"，应用到D列范围，关闭重开后看是否正常——如果手动的正常，那就是代码的公式拼接有问题。

三、重构后的健壮代码 #

我把代码改成了更可靠的版本，解决了上述所有问题：

Sub ApplyShopFreeConditionalFormatting()
    ' 指定目标工作表，避免依赖当前激活表（替换成你的工作表名称）
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("目标工作表")
    
    ' 每次执行都重新计算C列最后一行，确保范围最新
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row
    
    ' 直接定义D列目标范围，避免字符串拼接地址的隐患
    Dim targetRange As Range
    Set targetRange = ws.Range(ws.Cells(1, 4), ws.Cells(lastRow, 4))
    
    ' 先清除该范围的旧条件格式，避免规则堆积
    targetRange.FormatConditions.Delete
    
    ' 添加正确的条件格式：用相对行+绝对列的引用公式
    With targetRange.FormatConditions.Add(Type:=xlExpression, Formula1:="=$C1=""ShopFree""")
        .SetFirstPriority
        .StopIfTrue = False
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = RGB(128, 128, 128)
            .TintAndShade = 0
        End With
    End With
End Sub

然后把这个宏绑定到关键事件，确保覆盖所有需要更新的场景：

1. 文件打开时自动执行：在ThisWorkbook代码窗口添加：

Private Sub Workbook_Open()
    ApplyShopFreeConditionalFormatting
End Sub

2. C列内容修改时触发：在目标工作表的代码窗口添加：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 只在修改C列时更新规则
    If Not Intersect(Target, Me.Columns(3)) Is Nothing Then
        ApplyShopFreeConditionalFormatting
    End If
End Sub

3. 增删工作表时更新：在ThisWorkbook代码窗口添加：

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    ApplyShopFreeConditionalFormatting
End Sub

Private Sub Workbook_SheetDelete(ByVal Sh As Object)
    ApplyShopFreeConditionalFormatting
End Sub

四、关键改进点说明 #

• 指定工作表对象：彻底避免“当前激活表不是目标表”导致的错误。
• 每次重新计算最后一行：保证范围永远是C列最新的有效数据行。
• 清除旧规则：每次都重新生成干净的规则，不会堆积无效条目。
• 正确的公式引用：=$C1="ShopFree"用绝对列+相对行，Excel会自动把这个公式映射到D列的每个单元格，对应检查同一行的C列值，这种引用方式在文件重开后不会失效。
• 覆盖全触发场景：文件打开、C列修改、工作表变更时都自动更新，确保条件格式始终正确。

五、测试验证步骤 #

1. 保存文件后关闭重开，检查D列对应C列值为ShopFree的行是否变灰。
2. 修改C列某个单元格为ShopFree，确认D列对应行自动应用格式。
3. 按F9刷新，验证条件格式正常生效（现在应该没问题了）。

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