Conditional Formatting based on a selected range of data

Perodin

Board Regular
Joined
Sep 16, 2014
Messages
54
In one worksheet (Inventory) I have Item Numbers in column B that already exist in a database.

In another worksheet (Catalog 1) I have Item numbers and descriptions in column B that I would like highlighted if they contain the Item Numbers from the worksheet (Inventory).

My goal would be to see at a glance if entries in worksheet (Catalog 1) already exist in the database or not so that I may simplify my data management. Note that there are many catalog workbooks and I can either apply the formatting one at a time or as a whole. This should be easy, but with worksheet (Catalog 1) containing extended data I have not been able to find a way to accomplish my task. My data appears as such…

Raw Format
Worksheet (Inventory)Worksheet (Catalog 1)
12752458 Springs, front (black) (2) .............................................................. $2.00
15022458A Springs, front (white) (2) .............................................................. $4.00
37575857 Springs, front (white) (progressive rate) (2) .................................... $5.00
3757A3757 Springs, rear (black) (2) .............................................................. $2.00
58573757T Springs, rear (blue) (2) ................................................................. $4.00
65293757A Springs, rear (green) (2) ............................................................... $5.00
69213757X Springs, rear (white) (2) ............................................................... $4.00
7903A5858 Springs, rear (white) (progressive rate) (2) ..................................... $5.00
83024957 Springs, red (for Ultra Shocks only) (2.5 rate) (f/r) (2) ............... $2.50

<tbody>
</tbody>

Ideal Result
Worksheet (Inventory)Worksheet (Catalog 1)
12752458 Springs, front (black) (2) .............................................................. $2.00
15022458A Springs, front (white) (2) .............................................................. $4.00
37575857 Springs, front (white) (progressive rate) (2) .................................... $5.00
3757A3757 Springs, rear (black) (2) .............................................................. $2.00
58573757T Springs, rear (blue) (2) ................................................................. $4.00
65293757A Springs, rear (green) (2) ............................................................... $5.00
69213757X Springs, rear (white) (2) ............................................................... $4.00
7903A5858 Springs, rear (white) (progressive rate) (2) ..................................... $5.00
83024957 Springs, red (for Ultra Shocks only) (2.5 rate) (f/r) (2) ............... $2.50

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ztodd

Board Regular
Joined
Sep 17, 2014
Messages
221
What have you tried so far? Conditional formatting can do this for you- but you might want to insert a column and develop your formula in that new column until you get it working. Look up the help for these functions : VLOOKUP, LEFT, FIND, ISERR or ISERROR

You'll probably want to put the functions together in some way like this - ISERROR(VLOOKUP(LEFT(catalogentry,FIND(" ",catalogentry)-1), inventoryrange, ... ))

Try developing the solution yourself rather than using someone else's code or formula- it will help you out.

When figuring out a formula, work on it from the inside out- one piece at a time.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

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