Format duplicate cells based on selection from another workbook

Perodin

Board Regular
Joined
Sep 16, 2014
Messages
54
I have checked the forums for an answer and spent a few hours toying with functions, but I am newer to deeper formula craft. I am thinking this should be brutally easy and I am just not finding the right way to accomplish my task.

In worksheet "Inventory" I have a listing of values in B1:B1085 that are items currently in my database.
In worksheet "Catalog 1" I have a listing in of values in B1:B717 that are items I need to check against.

In short I want to highlight any cell in workbook "Catalog 1" that has a duplicate entry that occurs in worksheet "Inventory". However, I keep running into errors saying that I can't use direct reference to a worksheet range or that I can't use data contained on another worksheet. I would see this as a direct column to column compare, but cannot seem to make it work. I should also mention that there are multiple catalog worksheets I plan to check against (though I can do these one by one) and some values contain both letters and numbers as i know this affects how functions may be written.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Are you open to a VBA option? Also, are you comparing worksheets within a workbook or two workbooks? You switch back and forth through your post.
 

Perodin

Board Regular
Joined
Sep 16, 2014
Messages
54
Are you open to a VBA option? Also, are you comparing worksheets within a workbook or two workbooks? You switch back and forth through your post.

A VBA option would be fine, but writing it myself is still beyond me without much more practice.
Everything I want to compare is in 1 workbook, but separated into sheets. Sorry for not being more accurate.
 

Perodin

Board Regular
Joined
Sep 16, 2014
Messages
54
If its easier i could also drop the inventory data range into each individual worksheet, but would like to avoid this.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,597
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top