color format with conditional formatting
 

I have a sheet that contains trhee columns, one for an area, one for the
product in that area, and one for the amount of product. So column A lists
all of the areas and column B lists the product that is found in that area
and column C is the amount of the product. Column B and C are always
changing. Each product has a color assigned to it. So when the product
changes so does the color in column B and C. Since there are more than 3
products I used some code I found that works great for this. My problem is I
have another sheet that I would like to use as more of a visual. It is
designed in more of a layout format, than rows and columns so the cells that
are for the areas are somewhat sporatic. So I have a cell that says the area
and then the two cells underneath it contain the product name and product
amount. I am using a paste special to link the two worksheet's area
information, however the color doesn't seem to change if the product changes
on the original sheet. Also, is there any way I can tell it to also change
the color of the cells two down from the linked cell?? Or do I just need to
link them too.

I would appreciate any help you can give me.

Thanks!

at