conditional formatting with WILDCARD

[MR3]

is it possible to format a row with the condition NAME_* where name is always the same prefix?
thus if a row contains this particular prefix it will flood fill the entire cell

 

[Marcelo Branco]

Hi,

Maybe this

Conditional Formatting > New Rule > Use a formula ...
insert this formula
=SEARCH("NAME_",$A1)=1
pick the format you want

HTH

M.

 

[MR3]

But I want the code via a vb script so I can add it to my module t evaluate a specific row or column

 

[Marcelo Branco]

But I want the code via a vb script so I can add it to my module t evaluate a specific row or column

In which columns/rows are the names with NAME_ prefix?

M.

 

[MR3]

Column C and data could go all the way to the bottom of the worksheet

 

[Peter_SSs]

Does this do what you want?

If you are using Excel 2010 I don't think you need the ".Select" line.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ApplyCF()<br>    <SPAN style="color:#00007F">With</SPAN> Columns("C")<br>        .Select <SPAN style="color:#007F00">'2007 & earlier</SPAN><br>        .FormatConditions.Delete<br>        .FormatConditions.Add Type:=xlExpression, _<br>            Formula1:="=LEFT(C1,5)=""NAME_"""<br>        .FormatConditions(1).Interior.ColorIndex = 35<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Marcelo Branco]

This i got with the macro recorder to highlight (light grey) the rows in the range C1:F10 whose cells in column C begin with NAME_

Sub Macro3()
'
' Macro3 Macro
'
'
    Range("C1:F10").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=SEARCH(""NAME_"",$C1)=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.14996795556505
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Maybe yopu can adjust it to your real data

HTH

M.

 

[MR3]

THANKS Peter your code worked as intended.
Marcelo i will try your code next.

 

[MR3]

Does this do what you want?

If you are using Excel 2010 I don't think you need the ".Select" line.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ApplyCF()<br>****<SPAN style="color:#00007F">With</SPAN> Columns("C")<br>********.Select <SPAN style="color:#007F00">'2007 & earlier</SPAN><br>********.FormatConditions.Delete<br>********.FormatConditions.Add Type:=xlExpression, _<br>************Formula1:="=LEFT(C1,5)=""NAME_"""<br>********.FormatConditions(1).Interior.ColorIndex = 35<br>****<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hey Peter,
is there a way a user could Enter what the prefix should be, via a pop window in the vb script?
So if the user entered it I think it would have to auto fill in the length of the string entered and then it the script would need to search for and highlight the string entered in that particular column.

 

[Peter_SSs]

Try this:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ApplyCF()<br>    <SPAN style="color:#00007F">Dim</SPAN> Prefix <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> L <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Fbase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=LEFT(C1,#)=""^"""<br>    <br>    Prefix = InputBox("What prefix?")<br>    L = Len(Prefix)<br>    <SPAN style="color:#00007F">If</SPAN> L > 0 <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Columns("C")<br>            .Select <SPAN style="color:#007F00">'2007 & earlier</SPAN><br>            .FormatConditions.Delete<br>            .FormatConditions.Add Type:=xlExpression, _<br>                Formula1:=Replace(Replace(Fbase, "#", L), "^", Prefix)<br>            .FormatConditions(1).Interior.ColorIndex = 35<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>