How to highlight cell on column A if they include any of the strings on column B?

[sami770]

Hello everyone,
Please see excel file attached.
I have a simple question:
As you can see on the file attached, I have only 2 columns in the worksheet, Column A and column B, they both contain simple strings (words).
I would like to highlight all cells on column A that contain ANY of the strings on column B.

I prefer a macro based solution but if its much easier with a formula - that might work for me as well.

Many thanks,


Sami

[Cheeky Charlie]

This formula in conditional formatting:
=SUMPRODUCT(--NOT(ISERROR(SEARCH($C$1:$C$59,A1))))

Note I've referenced column C as column B entries have trailing spaces which will cause null result, C2=trim(B2)

[sami770]

Thanks for the fast response!
I did exactly what you suggest (see file attached) but it did not highlight the cells on column A
Did I miss something?

Sami

This formula in conditional formatting:
=SUMPRODUCT(--NOT(ISERROR(SEARCH($C$1:$C$59,A1))))

Note I've referenced column C as column B entries have trailing spaces which will cause null result, C2=trim(B2)

[Cheeky Charlie]

When I look at the formula in the conditional formatting at A1 I find:
=SUMPRODUCT(--NOT(ISERROR(SEARCH($C$1:$C$59,A1040509))))

1040509??!!

Select the whole column (click the A) and reapply the formatting with A1.

'Applies to' can be $A:$A (rather than include numbers giving an arbitrary limit)

[sami770]

Thank you!

When I look at the formula in the conditional formatting at A1 I find:
=SUMPRODUCT(--NOT(ISERROR(SEARCH($C$1:$C$59,A1040509))))

1040509??!!

Select the whole column (click the A) and reapply the formatting with A1.

'Applies to' can be $A:$A (rather than include numbers giving an arbitrary limit)

[xlbiznes]

Hi ,

use this code to get your job done. Also you need to fill the cell "C1" with a color that you will use to highlight the cells that match our search.

Hope this helps.

Happy Computing.

Please Login or Register  to view this content.

[AB33]

xlbus,
Please use code tags with your code. You can edit the above code by: Edit-Highlight the entire code, choose # from the quick reply menu

[xlbiznes]

noted, changed and thank you for letting me know.

[AB33]

xlbus,
You have "me" in your code. Is the OP supposed to put the code on sheet1, not on the standard module?

[xlbiznes]

AB33,
You need to paste this code on the code behind for sheet1.