Conditional Formatting If text String Contains x or y or z

[Bench]

I'n trying to work out a formula for Conditional Formatting a row to format the text BOLD if it contains a certain phrase or name within the cell.

So far i have made a list of the names i need, named the range "LG" and used the following code:

=ISNUMBER(MATCH($A1,LG,0))

However, this is too specific, it only picks up exact matches, my criteria of names will change constantly but will always have the constants of RBS, RBOS or Natwest contained within the cell, is there an alternative to MATCH that i can use that is less specific?

Thanks

 

[jbeaucaire]

You would need an array formula to be able to test the full range of names against the cell text, but CF doesn't accept array formulas. IF you want to test the actual names in your list, then do it in another cell with this array formula:

=OR(ISNUMBER(SEARCH(LG,A1)))
...confirmed in the cell with CTRL-SHIFT-ENTER

Then use CF in the cell that changes the cell's color if the cell above has a value of TRUE.
==========

If you can get by just testing for "RBS", "RBOS" or "Natwest" and that is sufficient, then you can use this CF formula:

=OR(ISNUMBER(SEARCH({"RBS","RBOS","Natwest"},A1)))

 

[barry houdini]

You can use "array formulas" in conditional formatting, you don't need any special entry like CTRL+SHIFT+ENTER, conditional formatting treats all formulas as potentially arrays

You can't use "array constants" like {"RBS","RBOS","NatWest"} within conditional formatting, so the named range is a good idea, this would be sufficient

=COUNT(SEARCH(LG,A1))

 

[Bench]

You can use "array formulas" in conditional formatting, you don't need any special entry like CTRL+SHIFT+ENTER, conditional formatting treats all formulas as potentially arrays

You can't use "array constants" like {"RBS","RBOS","NatWest"} within conditional formatting, so the named range is a good idea, this would be sufficient

=COUNT(SEARCH(LG,A1))

That's great, works like a dream and so simple too,

I amended it slightly to:

=COUNT(SEARCH(LG,$A1)

so that it formats the whole row

Thank you kindly

 

[jbeaucaire]

You can use "array formulas" in conditional formatting...conditional formatting treats all formulas as potentially arrays

You can't use "array constants" like {"RBS","RBOS","NatWest"} within conditional formatting, so the named range is a good idea, this would be sufficient

=COUNT(SEARCH(LG,A1))

Awesome. Thanks for the correction on this, I needed this.

 

[Bench]

Oops, my code should read

=COUNT(SEARCH(LG,$A1))

 

[Bench]

I'm trying to now write this as macro as this will be used by another user on a bi-daily basis, can anyone help, this is what i have so far but it doesn't seem to working. I've not quite got my head around declarations so i imagine my issue lies there.

Any guidance would be gratefully received.

As a reminder the purpose is to format any row that contains a part match to the criteria in RANGE "LG" bold.

Sub Macro1()
'
' Macro1 Macro
' Records Conditional Formatting  Macro recorded 01/10/2009 by ben.sharpe
'
'
Dim LG As Range
Set LG = Range("O2:O5")
    Columns("A:K").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNT(SEARCH(Range,$A1))"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
    End With
    Range("A1").Select
End Sub