Solved: macro to format cells

[jazznaura]

Hi all,
I?m trying to create a spreadsheet that shows where products are located in the warehouse. I?ve created one using mostly formulas, but its 3.5mb in size and takes about 5mins to calculate.
I wondering if it would be smaller in size and faster using a macro, in fact I?m wondering if it?s possible with a macro.
Can anyone advice me if it?s possible, would file size be smaller and would it be faster to run.
If the answer to all is yes, could someone please help me with this? My VBA knowledge is poor (recorded macros mostly) and would appreciate some assistance.
I?ve attached my workbook; on the ?layout? sheet you see the layout of the warehouse, with each cell representing a location in the warehouse and in each cell the name of the location. On the ?data? sheet you see in column ?A? locations and in ?B? identification codes for the products in that location. In columns ?G? to ?L? a table showing what type of product that code is.
This Data is imported from a text file.
What I would like to do is write a macro that looks at the cell locations in the ?data? sheet, determines their product type, and then formats the cell in the ?Layout? sheet with the same location name, with the colour indicated by the table in ?Data? sheet. Any locations not present in the ?data? sheet would be left with no formatting.
I don?t know if this is easy, difficult, or impossible. However, any info anyone can give me would be greatly appreciated.
Thanks.

[daniel_d_n_r]

This sounds like a perfect job for an access database

[jazznaura]

thanks for the reply daniel,

But my knowledge of access is zero to none.

i have however found the conditional formatting (more than three) info by DRJ in KBase, which looks hopeful and i'm trying to adapt that.

[mdmackillop]

Option Explicit

Sub ColourLayout()
Dim ws As Worksheet, rng As Range, c As Range
Dim Fmt As Range, Cel As Range
Set ws = Sheets("Data")
Set rng = Sheets("Layout").Range("A2:CV86").SpecialCells(xlTextValues)
Set Fmt = ws.Range("G2:L30")
On Error Resume Next
For Each Cel In rng
Set c = ws.Range("A:A").Find(Cel, lookat:=xlWhole).Offset(, 1)
If Not c Is Nothing Then
Cel.Interior.ColorIndex = Fmt.Find(c.Text).Interior.ColorIndex
End If
Next
End Sub

[jazznaura]

Thanks MDMMACKILLOP, thats greats, fast and small file size.

thanks to everyone who helped.