HOW TO highlight a list of words from an Excel sheet within a Word doc with a macro

I helped a relative who needed a list of words from an Excel sheet to be automatically highlighted within a Word document by writing this macro based on samples from the Internet:

	Option Explicit
	'References:
	'http://stackoverflow.com/questions/19071173/ms-word-macro-how-to-adapt-so-it-get-data-from-my-excel-file
	'http://www.mrexcel.com/forum/excel-questions/32187-convert-variant-array-string-array.html
	Dim TargetList() As String
	Public Sub GetWordsFromExcelAndHighlight()
	Dim lngIndex As Long
	Dim arrWords As Variant
	arrWords = GetListArray("C:\wordlist.xlsx")
	ReDim TargetList(UBound(arrWords, 1)) As String
	For lngIndex = 2 To UBound(arrWords, 1)
	TargetList(lngIndex) = arrWords(lngIndex, 1)
	Next
	Call Highlight
	End Sub
	Function GetListArray(ByRef strFileName As String) As Variant
	Dim xlapp As Object
	Dim xlbook As Object
	Dim xlsheet As Object
	Dim bAppStart As Boolean
	On Error Resume Next
	Set xlapp = GetObject(, "Excel.Application")
	If Err Then
	bAppStart = True
	Set xlapp = CreateObject("Excel.Application")
	End If
	On Error GoTo 0
	Set xlbook = xlapp.Workbooks.Open(FileName:=strFileName)
	Set xlsheet = xlbook.Worksheets(1)
	GetListArray = xlsheet.range("A1").CurrentRegion.Value
	xlbook.Close
	If bAppStart = True Then xlapp.Quit
	Set xlapp = Nothing
	Set xlbook = Nothing
	Set xlsheet = Nothing
	End Function
	Sub Highlight()
	Dim range As range
	Dim i As Long
	For i = 1 To UBound(TargetList)
	Set range = ActiveDocument.range
	With range.Find
	.Text = TargetList(i)
	.Format = True
	.MatchCase = True
	.MatchWholeWord = False
	.MatchWildcards = False
	.MatchSoundsLike = False
	.MatchAllWordForms = False
	Do While .Execute(Forward:=True) = True
	range.HighlightColorIndex = wdYellow
	Loop
	End With
	Next
	End Sub

view raw GetWordsFromExcelAndHighlight hosted with ❤ by GitHub

The macro can be called by a menu item from the Word document in the Ribbon or Quick Access Toolbar. I set the name of the main macro while configuring the option for the Quick Access Toolbar.

The Word macro will read words in a column  of a Excel sheet (from Column A of a sheet whose path is C:\wordlist.xlsx in the snippet and this can be changed) and then search & highlight all instances of these words or group of words within each cell that appear in the content of the Word document.

As a Office Macro programming newbie, I found this MSDN article useful for getting started.

Also see: HOW TO create a Ribbon-less pre-Excel 2007 look