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

Question

我有 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)当我调用宏时，我不断收到“无效的过程调用或参数”消息。

我做错了什么？

Answer 1

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

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

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

INDIRECT function

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

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

=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)")