Highlight duplicate cell in another sheet

Copper Contributor
I need some assistance if possible please.

Data in sheet 1, col B.
Data in sheet 2, col B.

If data in Sheet 2 Col B is the same as Sheet 1 Col B, highlight the duplicate in sheet 2.

13 Replies
best response confirmed by jvass92 (Copper Contributor)


Select column B on Sheet2. Cell B1 should be the active cell in the selection.

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

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

Enter the formula


=ISNUMBER(MATCH(B1, 'Sheet 1'!B:B, 0))


Substitute the real name of Sheet 1.

Click Format...

Activate the Fill tab.

Select a highlight color.

Click OK, then click OK again.

Thanks so much.

@Hans Vogelaar This worked perfect, even though numerous other sites said it was impossible. THANK YOU!



Thanks! I too have been searching for a solution to this. Everybody says it can't be done.

@Hans Vogelaar is there a way to highlight only if it matches two columns?


Currently working on an excel tool for work (logistics), and am trying to get our internal list of preferred origin zip & destination zip to auto-highlight on bid data so it is easy for us to identify our focus. 


I have two conditional formatting rules set up, but I would like to combine them into one rule and only highlight if both origin & destination match our master file.


Ideally, I'd be able to highlight any zip codes that are within a given radius of our internal data. Let me know if you have any work arounds or ideas!


How do you determine the distance?

For the sake of ease, I'd set both origin & destination radius at 50 miles each


Do you have that information in your workbook? If so, could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar I've linked the document. Was discussing with my boss and he thinks a VLookUp between two separate workbooks would work, but he's unsure how to set that up.


Open to any suggestions you have & appreciate the assistance!




I cannot access that file. You'll have to share it.

Best way for me to share it with you?


Click on my user picture and send me a private message. You should be able to attach the workbook there.