Databases
StackOverflow2013
Postsdbo
POExcel 2010 VBA Macro Conditional Formatting of row if cell contains text snippet
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel 2010 VBA Macro Conditional Formatting of row if cell contains text snippet
    text
    copied!<p>I'm doing conditional formatting in a macro (because I'm constantly applying it along with other formatting repeatedly to a fresh, raw export).</p> <p>Objective: highlight any row where the text in cell J(n) is "No Activity"</p> <p>Currently using:</p> <pre><code> 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 </code></pre> <p>...which works great. The above was cleaned up using a google search and a recording that originally gave me:</p> <pre><code> 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 </code></pre> <p>So I was feeling all proud and accomplished... <strong>but</strong> I also want to highlight rows (in a different color) where the cell in Column J (per above) <em>contains</em> "Quote" at any point in the text of the cell. </p> <p>When I recorded a macro of doing it as conditional formatting, it didn't really clarify anything for me: (ok, it made it worse)</p> <pre><code> 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 </code></pre> <p>I'm just not catching how it should change in </p> <pre><code>Type:=xlExpression, Formula1:= _ "=($J1=""No Activity"")" </code></pre> <p>All ideas greatly appreciated!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload