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

[derken73]

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

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Not sure what Conditional Formatting rule you were trying that crashed your system, but this CF formula works without any noticable delay for calculation.

=AND(LEN(B2)>0,ISNUMBER(MATCH(B2,$KJ$2:$KJ$9000,0)))

Applies to $B$2:$KH$250