Format cells in one sheet then vlookup and paste format into another

Excel 2007.

I have two related problems.

I need to assign multiple conditional formats depending on cell contents, but I don't think I can use conditional formatting....

Format_1.jpg

(Can't seem to post images..... probably as easy as registering )

Then on another sheet I want to pull across the "SKU" number via VLOOKUP to the appropriate "Locn." ID and then pull across the corresponding format for that SKU in "Con Form" column from Sheet 1:-

Format_2.jpg

Conditional formatting isn't transferrable across sheets, so I assume I will have to assign formats in Sheet 1 by string recognition in VBA.
Then there is the issue of copying over the formats to Sheet 2 which needs to be laid out in a non-tabular form (it is a representation of a floor plan and gaps need to inserted to represent walkways etc.). This is why I think I need a lookup function to perform the transfer. Sheet 1 however will be in tabular form.

Am I asking for the Moon?
Is it possible?

Be patient - I am a VBA Moron.....

Anybody.... Please?

Thanks for all the help.....

Cheers and goodbye.