Excel 2007 search across multiple workshets in one workbook for all duplicates (macro or VBA code?)

[Deleted]

Hello All:

I need assistance in setting up a conditional format (worksheet within the workbook) to conditionally format for duplicates looking at 15+ worksheets.  Just need to color both cells, not remove or otherwise change - just show the duplicate(s), if any. 

I suspect that it is a simple solution, but my knowledge of VBA is limited.  If a macro would do, then that is great.  I just need the simplest answer (as the user is not as savvy with Excel 2007).  Any and all help (directions, code, etc) would be most appreciated.

Thanks to everyone!

Scott

[#2]

@Deleted 

I am guessing you might like to check the same range in a number of different worksheets for duplicates. If so, you might use a Conditional Formatting formula like:

=(COUNTIF(Sheet1!A$1:A$10,A1)+COUNTIF(Sheet2!A$1:A$10,A1)+COUNTIF(Sheet3!A$1:A$10,A1)+...)>1

Such a formula is pretty ugly when you need to watch 15 worksheets, however.

As an alternative, you might consider using a different workbook layout so the values are reported back to different columns on the same worksheet. You could do so by copying down formulas like:

=IF(Sheet2!A2="","",Sheet2!A2)

Assuming that worksheet is called Master, you could use a Conditional Formatting formula in your target worksheets to test the values reported back to Master like:

=COUNTIF(Master!$A$1:$O$100,A1)>1