Text comparison with VBA

Hi all and a happy new year,

I am trying to compare 2 columns with many rows. I have tried to do it with conditional formatting but it is somehow not reliable. Now I am trying with VBA. What I want to do is this:

I have a column (e.g A) with many rows, each row contains a sentence and I have a column (e.g B) each row contains a single word. Now what I want to do is to check if a row in A contains any words from B. If such a row has been found then it schould be colored. In column C should be written the words found separated with comma. All should be case insensitive. Something like this:

Alice was beginning to get very tired of sitting by her sister on the bank, and of having nothing to do:	book
once or twice she had peeped into the Book her sister was reading, but it had no pictures or conversations in it,	peeped	peeped, book
`and what is the use of a book,' thought Alice `without pictures or conversation?'	nice	book

I have tried with this code but it doesn't work (Am a newbie in VBA...)

Please Login or Register  to view this content.

What should the code look like if B is in another sheet?

Thanks in advance.

Regards

Then need to see your REAL desired result in a workbook.

My guess..

Please Login or Register  to view this content.

excel_match2.pngexcel_match1.png

No Excel file, no help.

Here is the desired result in excel.

Here's a file with the code.

That's exactly what I need.

You are great. I love this forum. You saved my life twice.

Thank you very much.