基于另一个工作表中的公式设置条件格式

Question

基于另一个工作表中的公式设置条件格式

excelvbaconditional-formatting

3

我有两个 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）中的任何值匹配。当我调用宏时，我一直收到“无效的过程调用或参数”消息。

我做错了什么？

- Eunito

“=COUNTIF(base_valid!$B$6:$B$10|M2)>0”难道不应该是类似于“=COUNTIF(base_valid!$B$6:$B$10;M2)>0”的形式吗？你输入的是|而不是;(或者如果你使用的是英文版Excel，则应该是,)。 - Foxfire And Burns And Burns

尝试使用逗号和分号，但是我收到了相同的消息！:( - Eunito

那么 Set cond1 = rg.FormatConditions.Add(xlExpression, , "=COUNTIF(base_valid!$B$6:$B$10;M2)>0") 怎么办？将 COUNTIF 替换为您在 Excel 中使用的函数名称。 - Foxfire And Burns And Burns

这样我会得到“参数不可选”的错误提示。 - Eunito

它对我来说完美运行，你的Excel使用的是什么语言？另外，在你的Excel中，函数参数的分隔符是什么？是“;”还是“,”？请确保工作表“base_valid”存在，并确保范围“base_valid!$B$6:$B$10”未合并且不包含任何错误。 - Foxfire And Burns And Burns

好的，它对我起作用是因为我正在添加到同一工作表的引用。在条件格式规则中，您不能添加其他工作表范围的引用！所以 base_valid!$B$6:$B$10 引发了那个错误。手动尝试并弹出警报将告诉您。 - Foxfire And Burns And Burns

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Foxfire And Burns And Burns · Accepted Answer

在这里你需要小心。条件格式规则不允许手动添加到其他工作表的范围引用！会弹出警报提示！

弹出警告提示说“不能在条件格式中使用对其他工作簿或工作表的引用”，但Excel有另一个可以帮助的函数，称为INDIRECT。这个神奇的函数将允许我们引用不同工作表中的区域，但就像在同一工作表上一样。要引用一个范围，请使用引用名称作为字符串（文本）。因此，我们将使用以下公式作为标准：=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)")