Forum Discussion

Anonymous

Jul 09, 2019

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

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, n...

Cnditional Formatting Duplicates Multiple Sheets

Brad Yundt

MVP

Jul 10, 2019

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