Compare two Worksheets and Highlight Matches

I've been looking for some code to allow me to highlight matches in two columns in two seperate worksheets in the same workbook. Everything I've tried so far seems to just hang, almost immediately.

I have two worksheets, both with around 6,000 rows and 100 columns. Ideally I need something where the user can select the columns to compare, e.g. compare Column B on Sheet 1 with Column D on Sheet 2 by coming up with a userform or similar to allow the user to enter, which columns they want to compare with which on the two sheets and then for matching values to be highlighted on the relevant sheet, so if they are comparing Column B on sheet 1 with Column D on sheet 2, all matches, whether text or numB on sheet 1 and this would highlight the matches in Column D on sheet 2.

I found this, but it gives no status bar feedback as to progress and for my worksheets simply hangs:

Please Login or Register  to view this content.

I also found this, but it literally hangs almost immediately.

Please Login or Register  to view this content.

I have extremely limited knowledge of VBA, hence my need for help...

I'm not looking necessarily to adapt the code above, but if anyone has anything that would allow me to achieve the above with a simple user interface, i.e. a userform that allows the user to enter the columns and the worksheets to compare on both sheets, that would be very gratefully appreciated...

Many thanks

Hi protonLeah,

Many many thanks, this is brilliant and works perfecty. I have a couple of questions.

Firstly, when the three user forms are presented for input values to be entered, if the user clicks on the 'Cancel' button without entering anything, the code generates a 'runtime error '424' - object required' on all three userforms. Is it possible to tweak this, so the error doesn't appear, and it allows the user to cleanly cancel the operation?

I ran your code on my sheet and it took over an hour to compare two columns on two sheets with around 6,000 rows, is it possible to either add a modeless userform, that justs shows the text 'Calculating...' or to show the progress in the 'Status Bar' at the bottom of the page in some way, so the user can see that something is happening and they don't think Excel has hung?

The two sheets have the same headings in Row 1, which means that the headings come up as a match, which I don't want because the conditional formatting is also applied to the preformatted match column header, in the column where the 'X' is placed. If I want the user to still click the column heading for each input, but I want the matches to apply from row 2 down, how would I tweak your code?

Hi protonLeah,

Okay, I have the modeless userform working, with Andy Pope's help.

How would I go about repairing the error generated when the user clicks the cancel buttons on the input userforms and starting the comparison allowing for the header row as per the previous post?

Many thanks...

Hi Winon,

The input userforms are generated using VBA (I think), so there is no specific code for the Cancel button, if that makes sense. You'll have to excuse me, I'm really new to VBA...

This is the code that generates the user input forms..., so I'm not quite sure where I would add your additions?

Please Login or Register  to view this content.

Hello HangMan,

Sorry, but the code you posted boms out. Could you perhaps upload a "functional" updated WorkBook with the complete Code, so that I/we can have a look at it for you?

Thank you

Hi Winon,

There's a sample in the second post above, if you run it and then hit the cancel buttons on any of the three user input forms, you should get the error...

Many thanks...

The other thing I would like to 'add' if possible is the ability to enter different text, e.g. 'No Match Found' where currently blanks are displayed upon comparison, is this possible to do?

Hi Winon,

Perfect, that has solved that problem... Many thanks...

Let me know if you have any thoughts on the other thing. I'm trying to figure out how to adjust the code so the conditional formatting starts in Row 2 as I don't want it applied to the header row (Row 1), but ,y knowledge isn't sufficient it would seem!

Hi protonleah,

Okay, having added those lines, it still applies the conditional formatting to the header row! The conditional format formula it creates now is =NOT(ISBLANK($AC2:$AC6000)), but it is being applied to $AC:$AC and since there is a column header (text) in the row selected to put the matches, it applies the same conditional format to the column header as well. Does that make sense?

That works perfectly, thank you so much for all your help... I greatly appreciate it...

Is it okay to come back if any more questions crop up about this?