VBA - Macro Search, Highlight and Bold in red Predefined Keywords

[gentibr]

Hi,

I am looking for a macro (VBA) in Excel 2010 that when applied will search in Column A to find the keywords that are predefined in Column B and those that match will be bold and red color.

For the sake of providing an example below is the expected result when the macro is run:

Description of the Items	Keywords
System Production and Maintenance	Syst
Information and CRM Module	Inform
Hardware and Software Business Continuity Center	Hardware
Electronic Equipment	Electro
Data Center	CRM

<tbody>
[TD="class: xl66"][TABLE="width: 500, align: left"]
<tbody>[TR]

</tbody>

[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]Thank you in advance for your cooperation!

[/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

 

[svendiamond]

Sub changeColors()


With Sheets("Sheet2") 'or whatever your sheet name is


Dim keyWord As String, myLoop As Long, keyLoop As Long, myLength As Integer
Dim lastKey As Long, lastRow As Long, testCell As Range, startChar As Integer
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    lastKey = .Cells(.Rows.Count, 2).End(xlUp).Row


For myLoop = 2 To lastRow
Set testCell = .Cells(myLoop, 1)


    'test for keywords
    For keyLoop = 2 To lastKey
        keyWord = .Cells(keyLoop, 2).Value
        myLength = Len(keyWord)
        
        If InStr(testCell, keyWord) Then
            startChar = InStr(testCell, keyWord)
            .Cells(myLoop, 1).Characters(startChar, myLength).Font.ColorIndex = 3
        End If
    
    Next keyLoop


Next myLoop


End With
End Sub

 

[gentibr]

Wow that's great man. It really did the gig. One more thing, is it possible to also have it in bold.

Thanks a bunch!

 

[svendiamond]

Yeah just add it in right here:

         If InStr(testCell, keyWord) Then
            startChar = InStr(testCell, keyWord)
            .Cells(myLoop, 1).Characters(startChar, myLength).Font.ColorIndex = 3
            .Cells(myLoop, 1).Characters(startChar, myLength).Font.Bold = True
        End If

 

[gentibr]

Great job, Thanks a lot!