More efficient code for lookup based on cell value

**hilltop804** · 06-06-2013, 05:33 PM

Hi, everyone

I'm working on a spreadsheet that contains a list of products across the top columns and a list of stores down the left columns. Ideally, when an x is placed at the intersection of a store and product, this code should search down the information on the "data" tab to see if that combination of store number and product number exist. If it does, the cell should turn green and if it doesn't the cell should turn red. I'm very new to VBA, but have created a quasi-functional code. This crashes on the real worksheet, though, since the actual spreadsheet has about 1,000 item and store numbers on the "placements" tab and almost 200,000 rows on the data tab. I need some help making the code (first of all) work properly, and (second) making it usable on the real spreadsheet without crashing.

Please note, I cannot attach the real spreadsheet because it contains sensitive company data, but the example spreadsheet I made is a very similar replica and the code I'm posting is the exact same, aside from changing row and column values to compensate for it being smaller. Also note that I only changed the if result to make the background blue on the cells so I could see how far through the code it was making it (i.e. there will be no reason for it to turn blue once I have a fully functional code).

Example.xlsm

Please Login or Register  to view this content.

Please let me know what you think and I greatly appreciate your time,

Brad

**tigeravatar** · 06-06-2013, 05:57 PM

Brad,

Welcome to the forum!
I don't think you need VBA at all for this, just conditional formatting.
Attached is a modified version of your posted workbook.
The first thing I did was setup two dynamic named ranges, listStore and listItem.

listStore is defined with this dynamic named range formula:

Please Login or Register  to view this content.

listItem is defined with this dynamic named range formula:

Please Login or Register  to view this content.

You can view those by going to the Formulas tab and clicking Name Manager.

Next I select sheet 'PLACEMENTS' range D4:M13 and applied the following two conditional formats:
To highlight green (it exists):

Please Login or Register  to view this content.

To highlight red (it does not exist):

Please Login or Register  to view this content.

You can view the conditional format rules by going to the Home tab and clicking Conditional Formatting -> Manage Rules

If it absolutely has to be VBA, let me know and I'll provide a VBA solution, but this conditional formatting should work just fine for you.

**hilltop804** · 06-07-2013, 07:26 AM

Thank you so much! This is exactly what I was looking for. I dabbled with trying to solve it via conditional formatting, but couldn't figure out how to get it to work properly and started trying to solve it via vba instead.

Thanks again for the help,
Brad

Edit: If you were feeling up to it, and in order to help my understanding of the program, I also have a question for you. Why is the index function necessary in naming the ranges? Wouldn't A2:Max... create a range that was variable based on the amount of data in it?

**tigeravatar** · 06-07-2013, 10:34 AM

Index is used in order to define the last used cell in the column. The Max is just there to ensure that even if the column is blank, the 2nd cell would be the last cell is used. You could try it without the Index and see what happens, but you'll probably get errors.