Excel VBA为表格非连续多列应用条件格式时触发Run-time error '1004'问题咨询

这个问题我之前也碰到过，核心是两个原因导致的，咱们一步步拆解清楚：

1. 全局Range依赖活动工作表，触发上下文不一致 #

你在VBA编辑器直接运行时，活动工作表大概率就是包含tbl_caskinfo的那张表，所以Range()能正确解析结构化引用。但通过自定义功能区按钮触发时，Excel的活动工作表可能已经切换到了其他sheet，这时候全局的Range()会在错误的工作表里寻找不存在的结构化引用，直接抛出1004错误。

2. 非连续结构化引用的版本兼容性差异 #

Excel 2016对非连续的结构化引用写法（用分号分隔多个表列）支持比较宽松，但365/2019版本的解析逻辑更严格，这种写法会被判定为无效范围引用，所以不管怎么触发都会报错。而连续列的结构化引用（比如tbl_caskinfo[[Bottled]:[Bottled ABV]]）属于标准语法，所有版本都能识别，因此不会出问题。

修复方案：用明确对象引用+Union方法合并范围 #

直接修改你的代码，不要依赖全局Range，而是先定位到目标工作表和表格对象，再用Union把需要的列合并成一个范围——这种写法兼容所有Excel版本，而且完全不依赖活动工作表：

Sub ApplyConditionalFormatting()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    
    ' 明确指定包含表格的工作表（替换成你的实际工作表名称）
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' 获取表格对象
    Set tbl = ws.ListObjects("tbl_caskinfo")
    
    ' 用Union合并需要的非连续列范围
    Set rng = Union( _
        tbl.ListColumns("Act. Yield").Range, _
        tbl.ListColumns("Act. Bottles").Range, _
        tbl.ListColumns("Act. Final Payment").Range, _
        tbl.ListColumns("Act. Bottle Price").Range, _
        tbl.ListColumns("Bottled").Range, _
        tbl.ListColumns("Bottled ABV").Range _
    )
    
    ' 接下来执行你的条件格式逻辑，示例：
    rng.FormatConditions.Delete
    ' ... 你的条件格式设置代码 ...
End Sub

额外调试小技巧 #

如果你想确认是不是活动工作表的问题，可以在原来出错的代码行前加一句：

Debug.Print "当前活动工作表：" & ActiveSheet.Name

触发错误操作后，打开VBA的立即窗口（Ctrl+G）查看输出，就能直观判断是不是活动表不对导致的问题了。

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