compare two short lists for duplicates

Hi,

I have 2 short lists:

F2:F6 and F7:F11

and I need to do a quick check to see if any of the numbers in the first list match any of the numbers in the second list.

Is this possible without an array formula as I'll be putting it in a macro and looping it many times.

Any ideas?

Hey thanks. I tried:

Please Login or Register  to view this content.

copied down and that works alright.

However: I actually need to delete a block of cells out of there often and that changes the formula that are on another page. Do you know how to get around this?

Have you considered using Conditional Formatting to look for duplicates? No need for formulas, so no unwanted changes in formulas either.

I need the check in VBA, so conditional formatting can take a leap and turn itself green.

WOW!

Does:

Please Login or Register  to view this content.

Really produce the number of matches or 0 if none?

and I guess in VBA it would be:

Please Login or Register  to view this content.

Right?

Darn, that's not right. Excel doesn't like that VBA code. Anybody see the problem?