基于另一张纸中公式的条件格式 [英] Conditional formating based on formula in another sheet

问题描述

我有2个Excel 2007 PT-PT工作表：
一个（工作表1）有多个要验证的列。
另一个（base_valid）有5个要验证的列。
我正在尝试使用宏来验证 regioes（M2）列：

  Sub Validar_Regioes（） 
 Dim rg作为范围
 Dim cond1作为FormatCondition，cond2作为FormatCondition，cond3作为FormatCondition 
 Set rg = Range（ M2，Range（ M2）。End（xlDown））
 
'清除任何现有的条件格式
 rg.FormatConditions.Delete 
 
'为每种条件格式定义规则
 Set cond1 = rg.FormatConditions.Add （xlCellValue，xlExpression， = COUNTIF（base_valid！$ B $ 6：$ B $ 10 | M2）> 0）
 
'定义适用于每种条件格式的格式
 with cond1 
 .Interior.Color = vbGreen 
 .Font.Color = vbWhite 
结尾为
 
结束子
  

想法是检查M2列中的区域是否匹配base_valid地区范围内的任何值（base_valid！$ B $ 6：$ B $ 10）
调用宏时，我不断收到无效的过程调用或参数消息。

我在做什么错了？

解决方案

在这里很棘手。条件格式设置规则不允许添加对其他工作表上范围的引用，甚至不能手动添加！

哦，我在 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））
  

I have 2 Excel 2007 PT-PT sheets: One (sheet1) has multiple columns to be validated. The other (base_valid) has 5 columns to be validated. I'm trying to validate the "regioes" (M2) column using a macro:

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

The idea is to check if the region in M2 column matches any of the values in the base_valid regions range (base_valid!$B$6:$B$10) I keep getting "invalid procedure call or argument" message when I call the macro.

What am I doing wrong?

解决方案

You have to be tricky here. Conditional formatting rules don't allow to add references to ranges on other worksheets, not even manually! An alert will pop up!

The pop up alert says can't use references to other workbooks or worksheets for criterias in conditional formatting

But Excel got another function that can help, called INDIRECT

INDIRECT function

This wonderful function will allow us to reference a range in a different worksheet but as it where on same worksheet. To reference a range, use the reference name as string (as text)

So the formula we will be using as criteria is:

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

So your VBA code needs to be fixed like this:

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

This worked for me perfectly! After applying macro, I get this:

oh, my data in base_valid worksheet is like this:

Hope you can adapt this to your needs.

UPDATE DECEMBER 2019:

Thanks to @BigBen, another option is using a global-scoped named range, instead of indirect. In this case, you can create a name, let's say, MyValuesList, which references range base_valid!$B$6:$B$10.

Then, the CF rule would be =COUNTIF(MyValuesList;M2) and it would work properly.

So if you use a named range, there is no need of using INDIRECT.

Your code could be:

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

这篇关于基于另一张纸中公式的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！