基于另一张工作表中的公式的条件格式

我有 2 张 Excel 2007 PT-PT 表： 其中一个 (sheet1) 有多个要验证的列。 另一个 (base_valid) 有 5 列需要验证。 我正在尝试使用宏验证“regioes”（M2）列：

Sub Validar_Regioes()
    Dim rg As Range
    Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
    Set rg = Range("M2", Range("M2").End(xlDown))

    'clear any existing conditional formatting
    rg.FormatConditions.Delete

    'define the rule for each conditional format
    Set cond1 = rg.FormatConditions.Add(xlCellValue, xlExpression, "=COUNTIF(base_valid!$B$6:$B$10|M2)>0")

    'define the format applied for each conditional format
    With cond1
    .Interior.Color = vbGreen
    .Font.Color = vbWhite
    End With

End Sub

这个想法是检查 M2 列中的区域是否与 base_valid 区域范围中的任何值匹配 (base_valid!$B$6:$B$10) 当我调用宏时，我不断收到“无效的过程调用或参数”消息。

我究竟做错了什么？

你必须在这里狡猾。条件格式规则不允许添加对其他工作表上的范围的引用，甚至不允许手动添加！将会弹出警报！

弹出警报说不能使用对其他工作簿或工作表的引用作为条件格式中的条件

但是 Excel 还有另一个可以提供帮助的函数，称为 INDIRECT

间接函数 https://support.office.com/en-us/article/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261

这个奇妙的功能将允许我们引用不同工作表中的范围，但就像它在同一工作表上一样。要引用范围，请使用引用名称作为字符串（作为文本）

因此，我们将用作标准的公式是：

=COUNTIF(INDIRECT("base_valid!$B$6:$B$10");M2)

所以你的VBA代码需要像这样修复：

Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition
Set rg = Range("M2", Range("M2").End(xlDown))

Set cond1 = rg.FormatConditions.Add(xlExpression, , "=COUNTIF(INDIRECT(""base_valid!$B$6:$B$10"");M2)")

With cond1
    .Interior.Color = vbGreen
    .Font.Color = vbWhite
End With

这对我来说非常有效！应用宏后，我得到这个：

哦，我的数据在base_valid工作表是这样的：

希望您能根据您的需要进行调整。

2019 年 12 月更新：

感谢@BigBen，另一个选择是使用全局范围的命名范围，而不是间接的。在这种情况下，您可以创建一个名称，比方说，MyValuesList，参考范围base_valid!$B$6:$B$10.

那么，CF规则就是=COUNTIF(MyValuesList;M2)它会正常工作。

因此，如果您使用命名范围，则无需使用INDIRECT.

您的代码可能是：

Set cond1 = rg.FormatConditions.Add(xlExpression, , "=COUNTIF(MyValuesList;M2)")