highlight keywords in a list when they occur in a string on another sheet

Hi,

I have 3 columns of keywords in columns A, B & C on Sheet1. Each column has say 10 rows.
I also have a paragraph of text in cell A1 of Sheet2.
I would like to know if there is a way to highlight any keyword listed on sheet1 when it is present in the paragraph of text on sheet2.
If this is not possible, is there a way to return the cell numbers of any keywords found from the list and display those cell numbers on either sheet for reference?

Ideally I'd prefer not to use visual basic unless there is no other immediate solution.

Hopefully someone can point me in the right direction.

Thanks in advance!

A sample of your data with expected results will be most helpful.

Use "Go advanced" then Paper Clip icon

Thanks guys. I just left work and posted the query via mobile. As soon as I get home I'll attach an example and try the suggestion so far.

OK Thanks Glenn. I'm halfway there with your formula.

I've got 2 issues, maybe due to my ignorance.

1. When trying to apply conditional formatting to the range on sheet1 using your formula, I get the error message "you can't use references to other worksheets or workbooks for conditional formatting formulas"

2. Instead, if I place the paragraph of text directly on sheet1 (say in cell E1) then use the same formula for conditional formatting, I only get one hit. Cell A1 is formatted only. The rest appear to be ignored.

I've tried playing around with it but have had no joy.

Bebo - Attachment Book1 has no formula or conditional formatting in it. I'm not sure if you meant to just upload an example workbook as previously requested by John. Thanks anyway.

Cheers for the help so far.

My mistake. Your formula works perfectly when the paragraph of text is placed on the same sheet. I just forgot to reference the correct cell number where the text was pasted to.

Thanks again for the help guys.