Search multiple worksheets for keyword and return all rows with that keyword to new sheet

I know there are 100+ scripts out there for searching, but I have not found one that can search all worksheets for a keyword and bring all rows containing that keyword onto a new worksheet. If the term is in multiple instances within a row it should still only bring that row into the new worksheet once. However, multiple instances within a column should be sure to bring each row containing that keyword into the new sheet.

Style points if the new worksheet is titled the name of the search term such that running multiple searches keeps things organized.
Also style points for an 'exact' checkbox option that looks for the keyword as a stand-alone word as entered vs. looking for the search term buried within words. Ie. with box unchecked 'ham' returns rows with 'Birmingham'
Even more style points for color-coding the search term within the new worksheet.

If I've missed this (I've spent quite a few hours looking) please point me in the right direction. If you can help with a script, kudos and I imagine others would reference this in the future also.

Martin - thanks - this is slick.

I've been using this a bit more lately and with a larger workbook (36 worksheets) and have found that the results it returns are incomplete. I wish I could give you more specifics, but I haven't figured out why it chooses to skip certain rows. Running a manual CTRL+F find will find hundreds more unique instances than this script does, but it is a nightmare to try to consolidate those finds without the script. Any help would be much appreciated.

Ah ha - more details, it looks like the script stops searching any given worksheet as soon as it gets a hit, limiting the find capability to only one hit per worksheet. Is there any easy fix?

Originally Posted by mrice

Having a copy of your workbook and search terms would be useful in working out what is going on.

Afraid I can't do that. Database contains sensitive data that would not be easy to mask. I will see if I can build a mockup