Help with conditional formatting formula containing text

[barbilyn0875]

Hi! I'm new here, and I need help with a formula to use with conditional formatting. What I want to accomplish is to search one column for certain text in a string, then format another column with a specific color if the formula brings back a TRUE result. Quick and dirty example

If cell B2 contains the text "AB", then I want to color cell D2 a certain color.

Any help is appreciated!

Thanks!

 

[pgc01]

Hi
Welcome to the board

Select D2 and in the conditional formatting use the formula:

=$B$2="AB"

and set the format pattern

 

[barbilyn0875]

That would work if the cell contained only that text, but I am searching for "AB" in a string of text. Any further suggestions?

 

[pgc01]

If that's the case use a formula that checks if "AB" is contained in the string in B2, for ex., in the conditional formatting of D2 use the formula:

=FIND("AB",$B$2)

Remark: Find() is case sensitive. Else use Search()

 

[barbilyn0875]

pcg01,

Thanks for your quick reply. One more thing: I want to do this formatting all the way down the 2 columns, so I can't freeze the "AB" search to only one cell - it has to change to the next cell down each time.

 

[pgc01]

I'm not sure I understand exactly.

If you mean that D2 checks B2, D3 checks B3, etc., then don't freeze the row

In the conditional formatting of D2 use the formula:

=FIND("AB",$B2)

Copy the format down