Highlight duplicate cell in another sheet

[jvass92]

Hello,
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.

Thanks

[#2 · Accepted solution]

@jvass92 

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.

[#3]

Thanks so much.

[#4]

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

[#5]

@jvass92 

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

[#6]

@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!

[#7]

@Kcsurk 

How do you determine the distance?

[#8]

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

[#9]

@Kcsurk 

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?

[#10]

@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!

Workbook 

[#11]

@Kcsurk 

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

[#12]

Best way for me to share it with you?

[#13]

@Kcsurk 

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

[#14]

Sent!