Excel formula to find text from column B in column A

Hello,

I have the following situation.
In column A, I have many rows with let's say these values:
A1."abc123"
A2."abc143"
A3."abc454"
A4."abc566"
etc
In column B, I have many rows with text, and some of the rows will contain the exact text in the cells from column A.
For example:
B1."rfefhefeh abc123 efref"
B2."dhfherf abc566 thhewd"
B3."cjhuherffefehg abc454 gtgrhyjyu"
B4."werfje abc999 fhvdhfe"

Now, I need to highlight all cells in column A and all cells in column B that will match in values. In this example i need highlighted A1, A4, A3 and B1, B2, B3.
Is there any way in which i can get this done for a sheet containing approximately 10000 rows?

Thank you very much!!!

Hello,

I didn't specify that the value in column A will not be found in the same row on column B. It will always be on a different row. Also, not all rows will be highlighted in the end.
The data I need to remain with (what I'm doing is highlighting - checking - then deleting) is all rows that have values in column A that were not found in column B and all rows that have values in column B that were not found in column A.

Thank you!

You can use this formula for your first column (i.e. highlight the cells in column A, then click on Conditional Formatting | New Rule | Use a formula... , and enter this one):

=COUNTIF($B:$B,"*"&$A1&"*")

and this one for your second, with column B cells selected:

=SUMPRODUCT(--ISNUMBER(SEARCH($A$1:$A$4,$B1)))

Hope this helps.

Pete