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.
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,465
Messages
5,528,962
Members
409,848
Latest member
Blomsten
Top