Excel 2010 - Find Duplicates and mark each with different color in VBA
Posted on 2014-10-18
For a particular workbook, how would one write a macro that has the following conditions:
1. A Primary Worksheet: This contains the cell values to be searched.
a. This worksheet would have a specific name ("Error Items")
2. A Search Criteria: The value of each cell, within a range (eg. A7 to A:Lastrow), within the Primary Worksheet
a. Only one column of values is contained within the Range
3. A Search Loop: Through a set of secondary worksheets, to identify any duplicate values
a. There will be at least two worksheets, with the words "Posted Late" within the name on each Tab, or worksheet, to be searched.
4. Highlighting any Duplicate match that is found: The Interior Color of each cell match, is changed, or highlighted.
5. Each duplicate match has a different Interior Color: The interior color value, is incremented either up or down, so that the next match shows a different interior color of the pair, or set, than the one before it.
a. The color change doesn't have to totally different, but the color shades should allow it to be easily identified from the others.