Help Modifiying a Macro to Bold Specifc Text in a Cell

[odoualex]

Hi Everyone!

I need some help modifying this macro I've been using to bold and change color to specific text, Now I have a table with multiples columns with define terms, like 700 in the longest one.
Every week I receive a report of terms typed by the users into a database text fields, the report is around 8000 entries. the problem is that users are making all types of errors from spelling to using different terms which I have to correct before I can run any analysis on the data.

I want to use this macro to use the reference list to bold and change the color of the terms that have been correctly entered, so I can send the rest back for correction. I attached a sample, sheet 1 is how I receive the data and sheet 2 is the list of terms.

Please Login or Register  to view this content.

Thank you very much! in advance for your time!

I really will appreciate any help!

Alex

[AlphaFrog]

Have you considered using the Data Validation feature where the users fill in the data in a template with a Drop Down list or a Error message when there is an invalid entry?

Data Validation

[jindon]

Not really sure, but if want to change the color and bold the word in Sheet1 according to the list in Sheet2 then
Select the range that you want to change the font then run the code.

Please Login or Register  to view this content.

[odoualex]

Thank Your for responding to my request AlphaFrog!

Sorry, I forgot some details, the database where the user Type the entry is an Oracle database, I just get an excel report of all the data entered.
The user review medical notes and are suppose to identify those terms or synonyms and do the entry using free text field. I need to run analysis of the data entered, but I need to verify that all the terms are correct before doing any work with it, so I was using the macro to verify that I can work with the terms, initially was about 20 and I type all of them in the:
"inputWord = InputBox("Enter word to bold and color blue.") of the macro
Now the list is extensive and they are multiples Columns, I want to be able to identify the entries that doesn't match the reference list, and send them back for correction, I don't have control over the users entries.
The author of the macro in this part say that
"'SET UP ARRAY WITH WORDS YOU WANT TO COLOR AND BOLD - YOU COULD PUSH VALUES FROM A LISTBOX TO THIS ARRAY
Dim inputWord As String
inputWord = InputBox("Enter word to bold and color blue.")
myWords = Array(inputWord)
I thought that we can use the reference list instead of typing all the terms, but I have not idea how to start.

Again, Thank you very much for your valuable time!
I really appreciate it.

Alex

[odoualex]

Jindon!

Your macro work perfectly with the sample I posted like a magic!

Question, will it work with terms on multiples columns list in sheet 2, there are like 10 columns with specific group of terms or need some modifications?, I can't tested now because the data is at work, will test it as soon as I get there tomorrow morning.

Thank you very much for your interest in helping me with this matter, I really appreciate your time!

Respectfully,


Alex

[jindon]

Question, will it work with terms on multiples columns list in sheet 2, there are like 10 columns with specific group of terms or need some modifications?, I can't tested now because the data is at work, will test it as soon as I get there tomorrow morning.

Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

And let's see how it goes.

OOps, one more to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[odoualex]

Good Morning Jindon,

With the Changes I'm getting error "Run-time error "13", type mismatch in:

Please Login or Register  to view this content.

I run the initial code in the big data and work changing the color and bolding the identified terms within the text which is good, because I can see which ones has problems like:
F1:Notch
F1:Notches
in this case the first one is the correct term case the second entry the user added "es", the macro bolded and change the color of part of the entry leaving the "es" in black; I can see and advantage of this in some cases like this one. But I want the macro to validate the entry as it is in the reference list before making it bold and blue, so I can work with what is correct and reject the rest.

Probably is a lot to ask for.

Thank you for your time, I really appreciate it.

Respectfully,

Alex

[jindon]

The code should work as long as the List in Sheet2 has at least one data(excluding header) in each column.

Otherwise, I need to see your workbook.

[odoualex]

Hi Jindon,

Attached is a sample of the workbook with 3 columns in sheet 2.

Thank you for all your help!

I really appreciate it.

Respectfully,

Alex

[jindon]

OK,
1) Delete blank row, 2nd row from Sheet2.

2) If you want exact match then Pattern would change like this

Please Login or Register  to view this content.

[odoualex]

Jindon,

It work perfectly!

Exactly what I was looking for, you save me a lot of work.

I own you this one.

Thank you very much!

Alex