VBA - Macro Search, Highlight Red and Bold Predefined Keywords

[gittymoe]

I am looking for a macro (VBA) in Excel 2016 that when applied will search for the predefined keywords in Workbook "Keywords" Column A against Workbook "Voice" Column K to find the predefined keywords in Workbook "Keywords" Column A and those that match will be bold and red color. Sorry guys I could not figure out how to show the bold and Red color but if you could imagine in column B below the keywords ONLY from A would be Red & Bold (i.e. broke, chopped, slash).

Example below is the expected result when the macro is run:

Book1
	A	B
1	Keywords	Review
2	broken	I broke this just a few days ago and I really like it.
3	broke	This is now broken
4	chopped	This chopped the wood great
5	slash	We were able to slash prices
Sheet1

<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color:

 

[Akuini]

Ok, try this.
Workbook "Voice" must be active.
You may change the sheet name to suit.

[COLOR=blue]Sub[/COLOR] a1017209a[B]()[/B]
[COLOR=blue]Dim[/COLOR] va
[COLOR=blue]Dim[/COLOR] i [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR][B],[/B] x [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]
[COLOR=blue]
With[/COLOR] Workbooks[B]([/B][COLOR=brown]"Keywords"[/COLOR][B]).[/B]Sheets[B]([/B][COLOR=brown]"sheet1"[/COLOR][B])[/B]
va [B]=[/B] [B].[/B]Range[B]([/B][COLOR=brown]"A1"[/COLOR][B],[/B] [B].[/B]Cells[B]([/B]Rows.count[B],[/B] [COLOR=brown]"A"[/COLOR][B]).[/B][COLOR=blue]End[/COLOR][B]([/B]xlUp[B]))[/B]
[COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
 
[COLOR=blue]For[/COLOR] i [B]=[/B] [B][COLOR=crimson]2[/COLOR][/B] [COLOR=blue]To[/COLOR] UBound[B]([/B]va[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B])[/B]
   [COLOR=blue]With[/COLOR] Cells[B]([/B]i[B],[/B] [COLOR=brown]"K"[/COLOR][B])[/B]
      [COLOR=blue]If[/COLOR] InStr[B]([/B][B][COLOR=crimson]1[/COLOR][/B][B],[/B] [B].[/B]Value[B],[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] vbTextCompare[B])[/B] [COLOR=blue]Then[/COLOR]
         x [B]=[/B] InStr[B]([/B][B][COLOR=crimson]1[/COLOR][/B][B],[/B] [B].[/B]Value[B],[/B] va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]),[/B] vbTextCompare[B])[/B]
         [B].[/B]Characters[B]([/B]x[B],[/B] Len[B]([/B]va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]))).[/B]Font.Color [B]=[/B] vbRed
         [B].[/B]Characters[B]([/B]x[B],[/B] Len[B]([/B]va[B]([/B]i[B],[/B] [B][COLOR=crimson]1[/COLOR][/B][B]))).[/B]Font.Bold [B]=[/B] [COLOR=blue]True[/COLOR]
      [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]
   [COLOR=blue]End[/COLOR] [COLOR=blue]With[/COLOR]
[COLOR=blue]Next[/COLOR]
 
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

 

[gittymoe]

Akuni, Thanks I have plugged this into my module but I am not getting the desired results. It seems to find the word broke on the first row in column K but nothing else. I am sure I am not doing something right any insight?

Judd

 

[Akuini]

I assumed this:
The keywords are in Workbooks("Keywords") col A. The data is in Workbook "Voice" Column K.
Is that correct?

Did you change this part to suit: Workbooks("Keywords").Sheets("sheet1")?
The result in col K:

Review
I broke this just a few days ago and I really like it.
This is now broken
This chopped the wood great
We were able to slash prices

(Sorry, I can't post a table with colored font.)