Compare two columns with data, if match or not found then change the colour of cells

Hello I need to effectively compare two columns of serial numbers data (numbers+letters), because it will be a lot of them (more than 10000). The data are on two different sheets and both are in column A. What I need is to compare each cell with each other and if match then change the cell colour on both sheets to green, if not found then change the cell colour on both sheets to red. I have found very quick macro, but its not exactly what I need. Its shows unique values from each column in C or B column. I have tried to modify it on my own, but without success. So please can anyone look at it and modify it for me, thanks in advance.

Please Login or Register  to view this content.

Thank u for your prompt reply tehneXus u the MAN, this is exactly what I was looking for. Its very quick macro. I think its due to CreateObject("scripting.dictionary") you have used. This is something new for me. Anyway I try to understand your code, so it first change color of every cell to red.
Then in this part of code (below) it goes thru each cell one by one and when match it changes the color into green, right ?

Please Login or Register  to view this content.

But I need one last thing, because after a whole day I was unable to solve it on my own. So please can u add one last step: When the cell is found in both sheets (and changes its color into green), then it will select founded cell in sheet2, offset it from column B to F and copy this range into sheet1, next to matched cell (also into range B:F). Hope I explained it clear.

I very much appreciated your help, thank again in advance! I will need to take a deeper look at those d1, d2 objects scripting... because its looking very efficient and quick then simple ranges that I am using

Yes the list in sheet2 is unique. Many thanks again with helping me out with this, u make my day

Maybe if you have more time you could describe a little bit deeper each step of the code (it can also help others) , because I quite understand the first part, but from that passage: With xlWs2, For Each vKey In d2 keys I am totally lost

Your code its like a treasure of knowledge for me Nice Work!

AWESOME Much appreciated your explanation of code, thank you!