测试条件格式是否为真VBA并突出显示整个行

I am trying to determine whether or not a specific cell returns a TRUE value from conditional formatting, and then highlighting the entire row (or even just one cell) if it does return true. The conditional formatting is in column B, and would like it highlight the entire row.

我正在尝试确定一个特定的单元格是否从条件格式返回一个TRUE值，然后在它返回TRUE时突出显示整个行(甚至只显示一个单元格)。条件格式在列B中，并希望它突出整个行。

I am ultimately trying to add up numbers in column F if the conditional formatting returned TRUE in column B, but I can figure out that part if I can simply determine if the conditional formatting returned true or not. I have searched every forum, site, example, etc. I could find and still have not been able to make it work.

如果条件格式在B列中返回TRUE，那么我最终将在第F列中添加数字，但是如果我可以简单地确定条件格式是否返回TRUE，我就可以算出这一部分。我搜索了所有的论坛、网站、例子等等。我可以找到，但仍然不能让它工作。

I am running conditional formatting to search a large amount of data for multiple different instances. The only way I could figure out how to do it was to run each condition as a separate conditional format. Here is a portion of one of the subs (each sub has about 30 conditional formats and there are about 10 subs):

我正在运行条件格式，以便为多个不同的实例搜索大量数据。我能想到的唯一方法是将每个条件作为单独的条件格式运行。以下是其中一个子代的一部分(每个子代大约有30种条件格式，大约有10种子代):

 Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""Acadia Realty Trust "",B1))"
 Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
With Selection.FormatConditions(1).Interior
 .PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""Aimco "",B1))"
 Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
With Selection.FormatConditions(1).Interior
 .PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""Alexandria Real Estate Equities, Inc"",B1))"
 Selection.FormatConditions(Selection.FormatConditions.count).SetFirstPriority
With Selection.FormatConditions(1).Interior
 .PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

I only say that to say that I can't simply check the same test with conditional formatting, because there almost 300 different tests.

我只是说，我不能简单地用条件格式检查同一个测试，因为几乎有300个不同的测试。

UPDATE:

更新:

I tried what Jeep suggested, and then what I commented below building multiple subs, each with an array in order to fit all 264 conditions, but when I did so it only highlighted the last array, not all of the conditions that were met in the first subs. I used the same code as @Jeeped suggested below, but put 24 conditions in the array, and I can that code into 11 subs, and my primary code looks as follows:

我尝试了Jeep的建议，然后我在下面评论了构建多个潜艇，每个潜艇都有一个数组以满足所有264个条件，但当我这样做时，它只突出显示了最后一个数组，而不是第一个潜艇满足的所有条件。我使用了下面@Jeeped建议的代码，但是在数组中加入了24个条件，我可以将这些代码放到11个子中，我的主代码如下:

Public Sub REIT()
range("B:B").Select
Call A25
Call B25
Call C25
Call D25
Call E25
Call F25
Call G25
Call H25
Call I25
Call J25
Call K25
End Sub

I ended up just using two help columns to re-search the criteria and sum the data I needed, but I still have a problem with the highlighting.

最后，我只使用了两个帮助列来重新搜索条件并求和所需的数据，但高亮显示仍然存在问题。

1 个解决方案

#1

Build an array and use a loop. A With ... End With statement can handle the reference to the Application.Selection property.

构建一个数组并使用循环。一个与…End With语句可以处理对应用程序的引用。选择属性。

Option Explicit

Sub makeThreeCFrules()
    Dim v As Long, vCFRs As Variant

    vCFRs = Array("=ISNUMBER(SEARCH(""Acadia Realty Trust "", $B1))", vbYellow, _
                  "=ISNUMBER(SEARCH(""Aimco "", $B1))", vbYellow, _
                  "=ISNUMBER(SEARCH(""Alexandria Real Estate Equities, Inc"", $B1))", vbYellow)
    With Selection.EntireRow
        .FormatConditions.Delete
        For v = LBound(vCFRs) To UBound(vCFRs) Step 2
            With .FormatConditions.Add(Type:=xlExpression, Formula1:=vCFRs(v))
                .Interior.Color = vCFRs(v + 1)
            End With
        Next v
    End With
End Sub

The problem with your formulas was that you needed to anchor the column with $B1.

公式的问题是需要用$B1锚定列。

Alternate:

可选:

If you can put a list of the search terms somewhere else in the workbook, you could accomplish this with a single 'reverse wildcard lookup'.

如果您可以将搜索词列表放在工作簿的其他地方，那么您可以通过一个“反向通配符查找”来实现这一点。

^{Search terms in Sheet6!A2:A4}

搜索词在Sheet6 ! A2:A4

Option Explicit

Sub makeAllCFrules()
    Dim v As Long, vCFRs As Variant

    With Selection.EntireRow
        .FormatConditions.Delete
        With .FormatConditions.Add(Type:=xlExpression, _
                Formula1:="=SUMPRODUCT(--ISNUMBER(MATCH(""*""&Sheet6!$A$2:$A$4&""*"",$B1, 0)))")
            .Interior.Color = vbYellow
        End With
    End With
End Sub

It is critically important that no blank rows be included in the search terms range. If there are, you will be searching for a double wildcard and that will include everything. Of course, the supplied sub procedure could be modified to find the address of the search terms if they are subject to change.

最重要的是，在搜索条件范围中不包含空白行。如果有，您将搜索一个双通配符，它将包含所有内容。当然，提供的子过程可以被修改，以找到搜索条件的地址，如果它们可能发生变化。

^{With Conditional Formatting Rule applied after selecting B1:B99}

在选择B1:B99后应用条件格式规则

#1