Using conditional formatting to highlight duplicate rows (VBA?)

**RedditGold** · 02-10-2017, 03:01 PM

Hi All,

I'm dealing with a conditional formatting problem that has me pretty well stumped. In the attached workbook, I'm trying to apply some type of conditional formatting that highlights the identical rows that are found across each company. So for example, when you filter the data down by state (e.g. New York) it would present which rows are identical across all four companies. So New York would have "Fifth Avenue" and "Second Avenue" highlighted across all four companies, but the other rows would not be highlighted because there isn't a 1:1 match across column C and D. This conditional formatting could then be highlighted across each company's state when you filter the information down.
In the actual data, there can be thousands of rows of data that correspond to column B when you filter it down (e.g. Company A can have 300 results, Company B will have 390, etc).

What is the best way to go about doing this? I've been trying to come up with some VBA code, but to no avail.

Any ideas?

**dflak** · 02-14-2017, 03:27 PM

I think this is what you want.

I made a small pivot table from the data that contains the Company names. Then I manually filled the cells in the pivot table to match the colors on want in Column A for that company name.

The program goes down the cells in Table1[Company:], finds the name in the pivot table, gets the color associated with that name, and applies it to the cell.

I overlaid the pivot table with a named dynamic range so if you add or delete company names the color map adjust accordingly.
Color_Map =OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1).