Excel Macro To Color Certain Words In A Line Of Text

[western077]

Hello,
[TABLE="width: 100%"]
<tbody>[TR]
[TD]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-...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![/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I hope this is the correct link (I'm newbie to Dropbox...):

Yes, that is better, thanks.

In all our discussions "content_check" has been the named range containing the list of words that you want to highlight. In your case I believe that is the list in A1:A20385 of sheet 'Dizionario' & it was explained that there should be no blank cells in that named range.

In your workbook however, "content_check" is a named range that is E1:E2951 on sheet 'Cerca Testo' and it contains no words at all, one formula and 2950 blank cells!

You also said that content_check contained a list of words. In fact it contains words, phrases and punctuation marks. The punctuation marks in particular are a problem for my code as RegExp uses many punctuation marks for special purposes.

So, altogether, not too surprising that the code didn't work.

If you try the following steps in that sample file it should 'work', but whether it is exactly what you want I do not know.

1. Delete & re-enter (or edit) the current named range so that it does refer to Dizionario A1:A20385
   
   
2. Select column A on Dizionario and do a series of Find/Replace to replace each of these 7 symbols with nothing ! ? . ( ) [ ]
   (There may also be a problem - though not an error hopefully - with hyphens as you also have those in Dizionario, but that one is not quite so simple to solve)
   
   
3. Edit the code to replace "I4" with "A1" in two places
   
   
4. Run the code ?

 

[Ian Werich]

Yes, that is better, thanks.

In all our discussions "content_check" has been the named range containing the list of words that you want to highlight. In your case I believe that is the list in A1:A20385 of sheet 'Dizionario' & it was explained that there should be no blank cells in that named range.

In your workbook however, "content_check" is a named range that is E1:E2951 on sheet 'Cerca Testo' and it contains no words at all, one formula and 2950 blank cells!

You also said that content_check contained a list of words. In fact it contains words, phrases and punctuation marks. The punctuation marks in particular are a problem for my code as RegExp uses many punctuation marks for special purposes.

So, altogether, not too surprising that the code didn't work.

If you try the following steps in that sample file it should 'work', but whether it is exactly what you want I do not know.

1. Delete & re-enter (or edit) the current named range so that it does refer to Dizionario A1:A20385
   
   
2. Select column A on Dizionario and do a series of Find/Replace to replace each of these 7 symbols with nothing ! ? . ( ) [ ]
   (There may also be a problem - though not an error hopefully - with hyphens as you also have those in Dizionario, but that one is not quite so simple to solve)
   
   
3. Edit the code to replace "I4" with "A1" in two places
   
   
4. Run the code ?

Hi Peter,

Thank you very very very much! Now it's all ok!
I'm so sorry for all the mistakes I've done (I was astoundingly hurried).

So, one more time thank you.

Ian

 

[Peter_SSs]

You're welcome. Glad we got there in the end.