Excel: Excel Macro To Color Certain Words In A Line Of Text


I currently have a list of banned words which I have named "content_check" and are in a tab called "Guide". I would like to make a macro which will check cells G13, G15, G19, G21 and E30:E6000 (if not blank) in the tab "A+_Creation" and highlight in red any words which match those in the banned words list. These cells can contain up to 1000 characters each and could contain more than 1 banned word.I'm currently using conditional formatting which turns any cells which contain a banned word red, but given that the list has almost 400 words it would be much better if only the word turned red instead of the whole cell. I noticed that something similar has already been covered in this post: http://www.mrexcel.com/forum/excel-questions/775168-macro-bold-change-font-specific-words-cells-excel-2.html and I tried editing Rick Rothstein's code to match my requirements, but it just crashes when I try to run it. I have good knowledge of formulas, but I'm still quite new to macros and something like this is beyond my technical ability. Would anyone know how to go about this? Any help would be hugely appreciated!

This question generated 7 answers. To proceed to the answers, click here.

This thread is current as of October 12, 2014.

For more resources for Microsoft Excel