Using "Find" to return results if a word is found within multiple words in a cell

I have working code that prompt the user to enter a word, and then it will search in a specific column to find all the cases where the word is found. When a positive result is found, the whole line gets colored.

The code works but only if the word matches the cell content exactly.

Example:

Cell contains "Honey", user enters "Honey", the line will get colored.
Cell contains "Honey, Bees", user enters "Honey", the line will get ignored.

Here is the line of code that I think should be changed to something else, but I don't know what. I also tried to add stars (*) before and after the string that gets searched but it returned all the empty lines as positive results.

Please Login or Register  to view this content.

If you look up Find in Excel help you will see that there is a lookat parameter. You need to specify that if it is defaulting to something else:

Please Login or Register  to view this content.

Though don't think you need code at all, you could use conditional formatting.

Try:

Please Login or Register  to view this content.

Looks like I got "in" and "at" the wrong way round!

Thanks everyone, much appreciated. As for conditional formating, I'm building this worksheet for my computer-impaired boss and having him click somewhere and enter text without having to change anything is pretty much the most he wants to do. I'm an intern and I'm not even supposed to work with code so this should do the trick :D