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]

 

[Ian Werich]

It looks like I accidentally added two spaces after "CercaTesto"... if you copied my code line directly, modify it by removing those excess spaces and try it again, then let me know if it worked or not.

I've already noted these two spaces and typed correctly "CercaTesto", but the result is the same: no word is highlighted.
You must know that "content_check" contains more than 20,000 words.
I've tested again and deleted all the words except "cajetilla" and run again the code, but this word is not highlighted.
So I've moved "content_check" (containing the single word "cajetilla") to the same sheet to parse (i.e. "CercaTesto") and the code worked!
Could you let me know if the code can operate on limited named range?

Thank you very much for all your suggestions!
Ian

 

[Ian Werich]

You didn't give us the name of that sheet so for the adaptation of my code I have assumed 'Sheet2' for that named range. The adapted code is

Sub HighlightWords()
  Dim itm As Variant

  Application.ScreenUpdating = False
  With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = True
    .Pattern = "(\b| )(" & Join(Application.Transpose(Sheets("Sheet2").Range("content_check").Value), "|") & ")(\b| )"
    For Each itm In .Execute(Sheets("CercaTesto").Range("I4").Value)
      Sheets("CercaTesto").Range("I4").Characters(Start:=itm.firstindex + 1, Length:=itm.Length).Font.Color = vbRed
    Next itm
  End With
  Application.ScreenUpdating = True
End Sub

For example, here is my content_check (green cells) ..

Book1
	D
1	Content Check
2	good
3	DANCE
4	rhythm
5	singin'
6	I'd
7
Sheet2

.. and here is the result run over the words of American Pie

Book1
	I
4	A long long time ago I can still remember how That music used to make me smile And I knew if I had my chance That I could make those people dance And maybe they'd be happy for a while But February made me shiver With every paper I'd deliver Bad news on the doorstep I couldn't take one more step I can't remember if I cried When I read about his widowed bride Something touched me deep inside The day the music died So Bye, bye Miss American Pie Drove my Chevy to the levee but the levee was dry And them good ole boys were drinking whiskey and rye Singin' this'll be the day that I die This'll be the day that I die Did you write the book of love And do you have faith in God above If the Bible tells you so? Do you believe in rock and roll? Can music save your mortal soul? And can you teach me how to dance real slow? Well, I know that you're in love with him 'Cause I saw you dancin' in the gym You both kicked off your shoes Man, I dig those rhythm and blues I was a lonely teenage broncin' buck With a pink carnation and a pickup truck But I knew I was out of luck The day the music died I started singin' Bye, bye Miss American Pie Drove my Chevy to the levee but the levee was dry And them good ole boys were drinking whiskey and rye Singin' this'll be the day that I die This'll be the day that I die Now, for ten years we've been on our own And moss grows fat on a rolling stone But, that's not how it used to be When the jester sang for the king and queen In a coat he borrowed from James Dean And a voice that came from you and me Oh and while the king was looking down The jester stole his thorny crown The courtroom was adjourned No verdict was returned And while Lennon read a book on Marx The quartet practiced in the park And we sang dirges in the dark The day the music died We were singin' Bye, bye Miss American Pie Drove my Chevy to the levee but the levee was dry Them good ole boys were drinking whiskey and rye And singin' this'll be the day that I die This'll be the day that I die Helter skelter in a summer swelter The birds flew off with a fallout shelter Eight miles high and falling fast It landed foul on the grass The players tried for a forward pass With the jester on the sidelines in a cast Now the half-time air was sweet perfume While sergeants played a marching tune We all got up to dance Oh, but we never got the chance 'Cause the players tried to take the field The marching band refused to yield Do you recall what was revealed The day the music died? We started singin' Bye, bye Miss American Pie Drove my Chevy to the levee but the levee was dry Them good ole boys were drinking whiskey and rye And singin' this'll be the day that I die This'll be the day that I die Oh, and there we were all in one place A generation lost in space With no time left to start again So come on Jack be nimble, Jack be quick Jack Flash sat on a candlestick 'Cause fire is the devil's only friend Oh and as I watched him on the stage My hands were clenched in fists of rage No angel born in Hell Could break that Satan's spell And as the flames climbed high into the night To light the sacrificial rite I saw Satan laughing with delight The day the music died He was singin' Bye, bye Miss American Pie Drove my Chevy to the levee but the levee was dry Them good ole boys were drinking whiskey and rye Singin' this'll be the day that I die This'll be the day that I die I met a girl who sang the blues And I asked her for some happy news But she just smiled and turned away I went down to the sacred store Where I'd heard the music years before But the man there said the music wouldn't play And in the streets the children screamed The lovers cried, and the poets dreamed But not a word was spoken The church bells all were broken And the three men I admire most The Father, Son, and the Holy Ghost They caught the last train for the coast The day the music died And they were singing Bye, bye Miss American Pie Drove my Chevy to the levee but the levee was dry And them good ole boys were drinking whiskey and rye Singin' this'll be the day that I die This'll be the day that I die They were singing Bye, bye Miss American Pie Drove my Chevy to the levee but the levee was dry Them good ole boys were drinking whiskey and rye Singin' this'll be the day that I die
CercaTesto

#VALUE!

I've read just now your suggestion.
I'm going to test it.
Thank you

Ian

 

[Ian Werich]

I've read just now your suggestion.
I'm going to test it.
Thank you

Ian

Hi Peter,

I customized your code, but VBA returns an error (in italian it's "tipo non corrispondente"; I presume it sounds in english as "type non correspondent") as you can see in this thumbnail.

 

[Ian Werich]

Hi Peter,

I customized your code, but VBA returns an error (in italian it's "tipo non corrispondente"; I presume it sounds in english as "type non correspondent") as you can see in this thumbnail.

View attachment 2026
[/QUOT

I've already noted these two spaces and typed correctly "CercaTesto", but the result is the same: no word is highlighted.
You must know that "content_check" contains more than 20,000 words.
I've tested again and deleted all the words except "cajetilla" and run again the code, but this word is not highlighted.
So I've moved "content_check" (containing the single word "cajetilla") to the same sheet to parse (i.e. "CercaTesto") and the code worked!
Could you let me know if the code can operate on limited named range?

Thank you very much for all your suggestions!
Ian

I've finally found out the reason of the malfunction in my code: all the word in "content_check" must be written in lower case becasuse all the words in the text to parse are in lower case, not upper case, as they are in my not updated version of "content_check".
Thank you for your efforts!

Ian

 

[Ian Werich]

I've already noted these two spaces and typed correctly "CercaTesto", but the result is the same: no word is highlighted.
You must know that "content_check" contains more than 20,000 words.
I've tested again and deleted all the words except "cajetilla" and run again the code, but this word is not highlighted.
So I've moved "content_check" (containing the single word "cajetilla") to the same sheet to parse (i.e. "CercaTesto") and the code worked!
Could you let me know if the code can operate on limited named range?

Thank you very much for all your suggestions!
Ian

I've finally found out the reason of the malfunction in my code: all the word in "content_check" must be written in lower case becasuse all the words in the text to parse are in lower case, not upper case, as they are in my not updated version of "content_check".
Thank you for all your help!

 

[Peter_SSs]

Hi Ian

I'm not sure if your comments at the bottom of post 24 (your quote tags got a bit mixed up by the look of it ) about upper/lower case were in reference to my code but upper/lower case should not be an issue for my code (due to the line .IgnoreCase = True). You can also see in my sample in post 20 that in my dictionary list 'DANCE' was in upper case but the lower case occurrence of it in the text was coloured.

I have also just tested with the new sheet name and a list of 20,000+ words and received no error so I'm not sure what the issue is for you.

I would be interested to find the cause and wondering if it would be possible for you to upload a sample file that demonstrates the error (any sensitive information removed or disguised) to Dropbox, OneDrive etc and share a link here so I could investigate further?

How long is the text in I4 of 'CercaTesto'?

 

[Ian Werich]

Hi Ian

I'm not sure if your comments at the bottom of post 24 (your quote tags got a bit mixed up by the look of it ) about upper/lower case were in reference to my code but upper/lower case should not be an issue for my code (due to the line .IgnoreCase = True). You can also see in my sample in post 20 that in my dictionary list 'DANCE' was in upper case but the lower case occurrence of it in the text was coloured.

I have also just tested with the new sheet name and a list of 20,000+ words and received no error so I'm not sure what the issue is for you.

I would be interested to find the cause and wondering if it would be possible for you to upload a sample file that demonstrates the error (any sensitive information removed or disguised) to Dropbox, OneDrive etc and share a link here so I could investigate further?

How long is the text in I4 of 'CercaTesto'?

Hi Peter,

The text in I4 of "CercaTesto" is 1786 characters long.
I've uploaded a sample file named ESPERIMENTONE.xlsm to Dropbox, including a thumbnail of the runtime error that occurred (I'm a Office 2010 user)
In this sample the cell to parse is A1 and not I4.
Could you please tell me your userid for Dropbox in order to share the file?
Thank you.

Ian

 

[hernantorres23]

Hi Peter,

The text in I4 of "CercaTesto" is 1786 characters long.
I've uploaded a sample file named ESPERIMENTONE.xlsm to Dropbox, including a thumbnail of the runtime error that occurred (I'm a Office 2010 user)
In this sample the cell to parse is A1 and not I4.
Could you please tell me your userid for Dropbox in order to share the file?
Thank you.

Ian

Only put a link, using insert link tool, this located three position before insert code tool. Also you can send a message to them from messages tool, with a requesting

 

[Peter_SSs]

Could you please tell me your userid for Dropbox in order to share the file?

You need to post a publicly available link, not just one for me.

 

[Ian Werich]

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

Dropbox - File Deleted - Simplify your life

www.dropbox.com