Duplicates Highlight with conditional rule for range

Occasional Visitor

Hello, I would like to make a calculation or find a way to do this: 



in the picture above I have 2 different columns. 1 is Machines , and the other is Product. I would like to highlight the duplicates that are made by both machines (have at least once A1 and/or A2 in column A in the range of the duplicates) 
Something like (pseudocode style syntax here..!) if duplicates then check range of duplicates, if within that range there's A1 and A2 then highlight cell C or whole row. 
Is there a way to do this in Excel?

2 Replies



Maybe with this rule for conditional formatting.


Select the range you want to format conditionally, for example C2:C11, or rows 2 to 11 in their entirety.

The active cell in the selection should be in row 2.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula


Adjust the ranges if needed.

Click Format...

Activate the Fill tab.

Select a color.

Click OK twice.