VBA code for comparing a worksheet with a column and show the result with a color

Hello, I’m a newbie at programming in VBA So I hope someone here can help me along.

What I’ve been trying to do these last weeks is getting a nice looking overview of empty locations in our warehouse.
I Actually did get it to work(barely) with the conditional formatting function.
However since it has to compare about 9000 locations It grinds my 64bit Excel (i7 with 8GB) to a crawl and finally crashes.
I’ve attached the file I’ve been working with.
On sheet 1 you can see an export of our warehouse management system with all locations that contain material.
On sheet 2 you see the overview of all the possible locations.

My goal is to give all the locations on sheet 2 which have material on it a color.
The empty locations should remain the same.
I hope this is possible with VBA and that it doesn’t crashes excel when I use it

link to the file
http://dl.dropbox.com/u/350816/empty-locations.xlsx

@Derken73

Welcome to the forum.
Because I use E2003 I am not able to view the pallet layout beyond column IV. If you don't have a solution yet can you supply a wb showing positions from say column 'IL'.
There are so many cells in the range it is essential to 'map' the layout. It is possible to run a solution without mapping but as you have already found out the process can be incredibly slow or even fail.

Having been in logistics myself for a long time I am aware that racking configurations can be changed reasonably easily. Again from a 'mapping' point of view how 'stable' is this layout? Not that it matters to the solution but am I correct in assuming that each 3 outlined rows represents 3 tiers in reality?

If you can provide the missing detail I will continue with a potential solution.

Thank you.

gmk

@Derken73

Ok. This version does not require mapping of the warehouse pallet positions. FWIW with the data provided the process completed in 5.6 seconds. That should not crash your pc.

My version of Excel, limited to column IV, takes me nearly all the way through block 'F', (position KAF25G2) in your schematic. I have annotated the code to show how to complete block F and also how to extend the code for additional blocks.

Let me know how the solution fits or if you need assistance to extend.

Put all the code below into a standard module.

Please Login or Register  to view this content.

hth

gmk



Click the * below the post to say thanks and remember to mark the thread as solved if answered satisfactorily.