Highlight word based on workbook entries (Word 200

Topic

New Reply

(Edited by HansV to make URL clickable – see Help 19)

Dear All,

I found the macro that highlight misspelled words by Bill Coan at
http://word.mvps.org/FAQs/MacrosVBA/HighlightSpellErrors.htm%5B/url%5D

I also found Batch Replacer plug-in from ReplSoft website that replaces a list of words based on the entries in a worksheet list.

I would like to extend Bill’s macro and be able to do the hightlight function of Batch Replacer without closing/leaving my edited document, ie. reading column A of workbook “HighlightList.xls” and highlight all the words that appear in the workbook, in my current document.

Could anybody kindly assist?

Thank you in advance.

Rid

Reply | Quote

Replies

Just to be sure I’m understanding your question, you want a macro that looks at a currently open Excel worksheet named “HighlightWords.xls” and then highlights any occurrence of those words in the currently active Word document?

Reply | Quote

Spot on.

Hans has kindly provided some codes. I am testing them out now.

Thank you all.

Reply | Quote

See the attached code. Change the path of the workbook, and the name of the worksheet in the first lines of the procedure.

Reply | Quote

It works wonder Hans. Thank you so much.

Asking for your kindness further, how do I go about in modifying the codes so that:
1. Search all the texts in the current Word document based on the list of words in column A of Sheet 1
2. Replace the matching word in the Word document with corresponding cell B value (ie. if there is a text that matches cell A1 value, replace the text with cell B1 value), and highlight the replaced words.

Thank you again in advance. Really appreaciate all the helps.

Rid

Reply | Quote

Below the line

.Text = objWS.Cells(lngRow, 1).Value

insert this:

.Replacement.Text = objWS.Cells(lngRow, 2).Value

Reply | Quote

Maybe I am asking too much, but is it possible to maintain the original “case” (uppercase, lowercase, title case, etc) of the word without inserting all the possible combination in the worksheet? For example, with one single entry of word “abc” in A1 and “def” in B1, all the occurances of “abc”, “ABC”, “Abc” are replaced with “def”, “DEF”, “Def”, respectively.

Thank you.

Reply | Quote

Try changing

.MatchCase = False

to

.MatchCase = True

Reply | Quote

By changing the .Matchcase from False to True, those matching words but not matching case are not replaced anymore. But is it possible to have the best of both world? With single entry of “abc” in A1 and “def” in B1, all “ABC”, “abc”, “Abc” are replaced with “DEF”, “def”, “Def” respectively. In order words, I do not want to enter additional “ABC” in A2, “DEF” in B2; “Abc” in A3, “Def” in B3, and so on.

Cheers
Rid

Reply | Quote

You can store the lower case words in the worksheet, and add extra lines in the code to handle the variations, using UCase and StrConv. Look up those functions in the online help.

Reply | Quote

Thanks for the suggestion Hans.

It is a bit too much for a newbie like me, but will continue trying. For now, I will only use the multiple entries in the worksheet.

Thank you again,

Reply | Quote

I wasn’t thinking very clearly earlier on. If you set .MatchCase to False, Word should automatically do what you want, it is the default setting.

Reply | Quote

If you are ready for further adventures with Find and Replace, you could explore wildcard searching. This will let you “correct” all variant forms to one correct form. The thread surrounding post 457157 has more discussion.

Actually, upon further review, I’m not sure you want that feature. Hans probably has the simpler and better answer for your request.

Reply | Quote