如果单元格包含文本片段，则Excel 2010 VBA宏条件格式化行

I'm doing conditional formatting in a macro (because I'm constantly applying it along with other formatting repeatedly to a fresh, raw export).

我正在宏中进行条件格式化（因为我不断地将它与其他格式一起应用于新的原始导出）。

Objective: highlight any row where the text in cell J(n) is "No Activity"

目标：突出显示单元格J（n）中的文本为“无活动”的任何行

Currently using:

目前使用：

    With Cells
.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=($J1=""No Activity"")"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 7405514
            .TintAndShade = 0
        End With
        StopIfTrue = False
    End With
End With

...which works great. The above was cleaned up using a google search and a recording that originally gave me:

......效果很好。使用谷歌搜索和最初给我的录音清理上面的内容：

    Cells.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=($N1=""No Activity"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent4
    .TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False

So I was feeling all proud and accomplished... but I also want to highlight rows (in a different color) where the cell in Column J (per above) contains "Quote" at any point in the text of the cell.

所以我感到非常自豪和完成...但我也想要突出显示行（以不同的颜色），其中列J（上面）中的单元格在单元格文本中的任何点包含“引用”。

When I recorded a macro of doing it as conditional formatting, it didn't really clarify anything for me: (ok, it made it worse)

当我录制一个宏作为条件格式时，它并没有真正为我澄清任何事情:(好吧，它让它变得更糟）

    Selection.FormatConditions.Add Type:=xlTextString, String:="Quote", _
    TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False

I'm just not catching how it should change in

我只是没有意识到应该如何改变

Type:=xlExpression, Formula1:= _
"=($J1=""No Activity"")"

All ideas greatly appreciated!

所有想法都非常感谢！

1 个解决方案

#1

This works in Excel 2010:

这适用于Excel 2010：

With Cells
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=($J1=""No Activity"")"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 7405514
            .TintAndShade = 0
        End With
        StopIfTrue = False
    End With

    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=ISNUMBER(SEARCH(""*quote*"",$J1))"
    With .FormatConditions(.FormatConditions.Count)
        .SetFirstPriority
        With .Interior
            .PatternColorIndex = xlAutomatic
            .Color = 4405514
            .TintAndShade = 0
        End With
        StopIfTrue = False
    End With
End With

Obviously you'd need to change the Color for the 2nd FormatConditions.Add section.

显然，您需要更改第二个FormatConditions.Add部分的颜色。

Edit: Realized you were looking for "Quote" anywhere in the cell, so I've updated the code from my original posting.

编辑：已经意识到你在单元格的任何地方都在寻找“引用”，所以我已经更新了原始发布的代码。

#1