I am trying to improve a macro that searches for a string in a user-defined range in Excel that provides the option of matching whole words in the search. (I want the match whole word search to look for a search term that does NOT have a letter or number immediately before or after it and therefore interpret that as a whole word match.)

I have several macros I’ve been working on in the attached (destined for a userform which I will set up after I get the code to work as intended).

The ‘Find_and_Highlight3’ macro works exactly as I want it to – it finds all instances of the search with both case sensitive and insensitive options and formats by size, bold, and text color. I ran it using a font size of 16, bold, and the font color of red (and case insensitive). It found and formatted every occurrence of “test”. The only thing that’s missing is the option to search by whole word matches. That’s why it formats the ‘testT” in 'Avicenna writesThe Book of Healing and The Canon of Medicine’. I want to have a match whole word user option that would ignore such an instance.

‘Find_and_Highlight4’ is my attempt to incorporate a ‘RemoveSpecial’ function that was written for me for a request in February to add match whole word functionality to a search an extract macro (http://www.msofficeforums.com/excel-...hes-whole.html). It is supposed to look for "\/:*?™""®<>|.&@#(_+`©~);-+=^$!,'" characters before or after the search term and accept it as a whole word match if found. But I can’t get it to find anything with this format macro.

The ‘Test_Whole_Word_Match’ I found online and it does a great job of identifying whole word matches (through a msgbox) but I am too VBA-dumb to figure out how to write code to make it format the found text if it matches a whole word search.



Can someone help with enabling the ‘‘Find_and_Highlight3’’ to include a whole word match search?

Attached Files

Sample File for Find and Format macro.xlsm (121.8 KB, 15 views)