Highlighting words within a string if they appear on a list?

[BDexcel]

Can someone help please?

I have a list of boxes containing strings of text, I need to highlight any words appearing in these strings if they appear in my list... is this possible?

For example:

Sentence	List
My motorbike is yellow	yellow
My car is red	red
	car

<tbody>
</tbody>

Hope this makes sense any help appreciate

 

[Peter_SSs]

So, would the text in the 'List' column need to be at the very start of a word in the sentence column, or could it be anywhere?
For example, if the 'List' contained "car" and the sentence was "At the carnival I was incarcerated" would the result be

a) At the carnival I was incarcerated
or
b) At the carnival I was incarcerated

 

[BDexcel]

Very good point Peter... but I feel if it was anywhere within the string, like your example b would be perfect. I appreciate its an odd request and really appreciate your help.

 

[Peter_SSs]

I feel if it was anywhere within the string, like your example b would be perfect.

So I think we are not looking for "words" at all & results very much like Mick provided at the start, apart from highlighting multiple instances of the "List" strings if they occur in the "Sentence" cells.

Try just changing the Pattern line to

RX.Pattern = Join(Application.Transpose(Range("B2", Range("B" & Rows.Count).End(xlUp)).Value), "|")

 

[BDexcel]

Peter this works perfect! tested it on a couple of tabs and it seems perfect. Thanks so much

I may need to amend slightly to pick up the list from other columns etc. but i'll try see if i can get it to work.

Really appreciate your help!

 

[Peter_SSs]

Really appreciate your help!

Cheers, Glad to help.

 

[BDexcel]

Guys one last question on this (hopefully)

The code Peter provided works great! I have it working and its perfect. The only change i have been asked to make is change my 'List's' as im working with more than one onto another sheet.

Is it possible to change the lookup range in the below for example to B2 in sheet 2? Hope that makes sense?

RX.Pattern = Join(Application.Transpose(Range("B2", Range("B" & Rows.Count).End(xlUp)).Value), "|")

 

[Peter_SSs]

Is it possible to change the lookup range in the below for example to B2 in sheet 2? Hope that makes sense?

Sure, but since there are multiple sheets, it would be safest if we knew the name (or position) of the sheet with the data that is to be highlighted so the code doesn't start trying to highlight text on the wrong sheet.
- Does that sheet always have a particular name? or
- Is that sheets always, say, the left hand sheet in the workbook?, or
- Does it possibly have different names and position but is always the only other sheet besides 'Sheet2' in the workbook? or
- Can we be sure that it will always be the 'Active' sheet when the code is run? or
- Is there some other way that we can always use to identify the correct sheet (eg a particular heading in the sheet)?

 

[BDexcel]

Thanks Peter the sheet where the list will be placed will always be named ‘lexicon list’. Let’s say the list is in column A starting in cell A2.

Would it be possible to reference this?

 

[Peter_SSs]

.. the sheet where the list will be placed will always be named ‘lexicon list’.

I'm not yet confident that answered my question. Does 'lexicon list' house what we originally had in the 'Sentence' column or the 'List' column?

My previous post was all about the sheet with the 'Sentence' column. So if 'lexicon list' houses the original 'List' column then please review my previous post and answer that in relation to the sheet containing the 'Sentence' data.

 

[BDexcel]

Sorry for the confusion Peter.

Yeah so ‘lexicon’ will house the list, hope that helps. Really appreciate your help with this