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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sentence [/TD]
[TD] List[/TD]
[/TR]
[TR]
[TD]My motorbike is yellow [/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]My car is red [/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]car[/TD]
[/TR]
</tbody>[/TABLE]

Hope this makes sense any help appreciate

 

[Peter_SSs]

So, 'lexicon' (what column?) will house the list of sentences and 'lexicon list' (A2 & down) will house the list of text strings to search for in those sentences?

 

[BDexcel]

Yeah peter

My tab with the sentences is named ‘Raw data’ but itl doesn’t matter I can change to lexicon or leave as raw data whatever is easiest for you.

Your correct ‘lexicon list’ will house the list from A2 and down.

Thanks again for ur support!

 

[Peter_SSs]

whatever is easiest for you.

What is easiest for me is for you to be a bit more careful about answering my questions. I'm not trying to be rude saying that, just trying to understand what you have, where and what you are trying to achieve. To be able to test what I give you I need to be able to set up a sample like yours.

In post #16 you said that the 'List' items were on "sheet 2", though in fact it now seems to be 'lexicon list'
Since post #17 I have been trying to ascertain the name of the other sheet (where the sentences are). I had to press that point several times but at least we now have that: "Raw data"
In post #21 I asked what column the sentences are in. You have not addressed that question as yet.
And an extra question: Whatever column you tell me for the above, does that text to be checked & highlighted also start in row 2?

 

[BDexcel]

Apologies Peter didn’t mean to be unclear.

Your correct in what you have stated:
- The list will be in a sheet called ‘lexicon list’, listed in cell A2 and down.
- The sentences are on a sheet called ‘Raw data’, and will be in column E startintony in row 3 so cell E3 and down.

Apologies again for the confusion

 

[Peter_SSs]

- The list will be in a sheet called ‘lexicon list’, listed in cell A2 and down.
- The sentences are on a sheet called ‘Raw data’, and will be in column E startintony in row 3 so cell E3 and down.

OK, thanks, that is much clearer. .

Try this version.
One point that I don't think I mentioned before. The list in column A of 'lexicon list' must not have blank cells among the data. If there is, this code will highlight everything in E3 down in 'Raw data'. If there is any possibility of blanks in 'lexicon list' column A, post back as a modification can be made to avoid the problem.

Sub HighlightWords_v2()
  Dim RX As Object, Mtchs As Object
  Dim itm As Variant
  Dim c As Range
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  With Sheets("lexicon list")
    RX.Pattern = Join(Application.Transpose(.Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Value), "|")
  End With
  Application.ScreenUpdating = False
  With Sheets("Raw data")
    .Columns("E").Font.ColorIndex = xlAutomatic
    .Columns("E").Font.Bold = False
    For Each c In .Range("E3", .Range("E" & .Rows.Count).End(xlUp))
      Set Mtchs = RX.Execute(c.Value)
      For Each itm In Mtchs
        With c.Characters(Start:=itm.firstindex + 1, Length:=itm.Length)
          .Font.Color = vbRed
          .Font.Bold = True
        End With
      Next itm
    Next c
  End With
  Application.ScreenUpdating = True
End Sub

 

[BDexcel]

Peter thank u so much! Honestly appreciate ur assistance.

I will try this once I’m back at my computer. You are correct I did notice previously it highlighted everything and as you said it was due to a blank cell within my list but I removed it and it worked fine afterwards. Even though there is blank cells below my list it was only an issue when the blank cell was amongst the list.

Ur a gent!! Thanks again for ur help and I’ll try this ASAP

 

[Peter_SSs]

If you might have blanks in the 'lexicon list' column A range ..
Add the extra (blue) line of code where shown, and
Replace the red one with this

RX.IgnoreCase = True
RX.Pattern = "\|{2,}"
With Sheets("lexicon list")
  RX.Pattern = RX.Replace(Join(Application.Transpose(.Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Value), "|"), "|")
End With

 

[BDexcel]

Peter, thanks so much! this works perfect now

Your a VBA guru!

 

[Peter_SSs]

Peter, thanks so much! this works perfect now

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