Find and highlight/bold/underline/colour a block of matching text (is this possible?)

**joelhuang** · 06-10-2011, 03:23 AM

Hi,

I would like to create a macro that will search words in Column A against Column B for text, and once found, will highlight the match in column B. Is this possible?

As it is easier to show an example, I have created an example of what I'm trying to do. Please kindly refer to the example and guide me along.. Thank you very much!

**arthurbr** · 06-10-2011, 03:29 AM

No example attached

Have you tried Conditional Formatting ?

**joelhuang** · 06-10-2011, 03:34 AM

Hello, what a coincidence, I was just amending the example when you replied

Yes, I tried it but I can't possibly do it for every cell...

**joelhuang** · 06-13-2011, 02:56 AM

Hi, could someone please give me some assistance here? Thank you...

**Haseeb Avarakkan** · 06-13-2011, 03:09 AM

Hello Joelhuang,

If you are using this formula in conditional formatting, this will highlight the whole cell, not the particular word. if you want to highligh or Bold the words, would require VBA, hope some one will get to you.

Select the range, B5:B10, then try this formula in CF,

Please Login or Register  to view this content.

Choose the format.

**Andy Pope** · 06-13-2011, 03:25 AM

Please Login or Register  to view this content.

**joelhuang** · 06-14-2011, 04:34 AM

Andy that was amazing! I tried reading the book for dummies and only had a rough idea, but to put it to work is totally different! Thank you~

**joelhuang** · 06-14-2011, 04:35 AM

Thanks too Haseeb :D

**joelhuang** · 07-13-2011, 01:51 AM

Hi, is this enhancement possible?

instead of highlighting only the second column, the matches are highlighted in both columns while the script is searching for matches (example attached).

Thanks for any help!

**Andy Pope** · 07-13-2011, 03:48 AM

Please Login or Register  to view this content.

**joelhuang** · 07-13-2011, 06:37 AM

Again, thanks!

**joelhuang** · 07-13-2011, 06:55 AM

Just for knowledge's sake, is it possible to indicate which 2 columns to compare, so that I can do it directly on the data and don't have to copy them onto another workbook and paste in Column A and B?
Very curious about the usage of relative reference, but it always confuses me..

**Andy Pope** · 07-13-2011, 06:56 AM

If you select the first column of the table then you can use

Please Login or Register  to view this content.

**joelhuang** · 07-15-2011, 03:27 AM

I see... So if I were to select, say, Column B and G, this will work as well to compare Column B against Column G?

**Andy Pope** · 07-15-2011, 03:37 AM

Selection of columns B and G would be a non contiguous selection and require the use of the Areas object.

try this (untested),

Please Login or Register  to view this content.

**joelhuang** · 08-01-2011, 05:33 AM

Hello Andy,

It's me again...

It seems that duplicate words in the second columns will only be highlighted once, while those in the first column are always highlighted even if they are duplicated words. I'd need to highlight all words including duplicates, so is there a way to go about it?

Eg.
Raw Data
Column A_____________________________________Column B
Ma Tai Tai from China loves to drink Tai Tai Soup_____Ma Tai Tai in Japan hates the smell of Tai Tai Soup

Desired outcome
Column A_____________________________________Column B
Ma Tai Tai from China loves to drink Tai Tai Soup_____Ma Tai Tai in Japan hates the smell of Tai Tai Soup

Actual Results
Column A_____________________________________Column B
Ma Tai Tai from China loves to drink Tai Tai Soup_____Ma Tai Tai in Japan hates the smell of Tai Tai Soup

**Andy Pope** · 08-01-2011, 09:21 AM

You need to loop rather than do the code once.

Please Login or Register  to view this content.

**joelhuang** · 08-02-2011, 03:22 AM

So brilliant! Thank you :D

How about this: it is best to keep it to scan for A1-A10, as it will take too much time and memory to run for A10000 all the time. However, the number of data ranges from A500 to A1000.

Can I do this to make it semi-automatic?

eg.

1) I will do a check to see what is the last cell of Column A that contains data.

Please Login or Register  to view this content.

2) The code will then be:

Please Login or Register  to view this content.

May I know if this is possible too? I tried reading vba for dummies, but don't think I saw the method of detecting the last cell being taught..

Currently, I have the following as a temporary measure:

Please Login or Register  to view this content.

**Andy Pope** · 08-02-2011, 07:44 AM

Please Login or Register  to view this content.

Find and highlight/bold/underline/colour a block of matching text (is this possible?)

LinkBack

Thread Tools

Rate This Thread

Display

Find and highlight/bold/underline/colour a block of matching text (is this possible?)

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Re: Find and highlight/bold/underline/colour a block of matching text (is this possib

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions