Jul 09 2019 06:59 AM
Jul 09 2019 06:59 AM
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
Jul 09 2019 06:14 PM - edited Jul 09 2019 06:22 PM
@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