Automatic conditional formatting (color) for similar spreadsheets

[meps]

Hey there, I'm a pretty big Excel noob so I hope I can explain what I need properly. This would help us out a lot at work!

So, basically, we run tests on a tasting panel of 10 three times a week. The software we use is called EyeQuestion and it spits out an Excel document that looks like this:

http://i.imgur.com/9hSBoEZ.png

What we then usually do is manually set up conditional formatting so we can easily see what numbers are in what cells. It's all based on the numbers of the products (as you can see each product has two codes in the screenshot, however they might have more - or less). We then print it out and use it to keep track of which products to serve when etc etc.

In the end it might look like this (which codes get which color doesn't matter, as long as they're all different):

http://i.imgur.com/Gikpjov.png

So, it takes quite a bit of time to manually assign a color for each of the codes it spits out. Is there a way of automating this? Maybe with a macro or something?

Appreciate all help greatly.

[etaf]

probably need a macro - but just an idea - is the layout always the same and so you only have 8 products, and always in the same cells
if so then you may be able to setup the conditional formatting in a master spreadsheet and then copy > paste special > format into the new sheet created
But it would need to always be an identical layout , and just the numbers change

[meps]

probably need a macro - but just an idea - is the layout always the same and so you only have 8 products, and always in the same cells
if so then you may be able to setup the conditional formatting in a master spreadsheet and then copy > paste special > format into the new sheet created
But it would need to always be an identical layout , and just the numbers change

Hey! No, sadly the amount of products change all the time, but at least they end up in predictable cells, ie. under the other ones. And the codes change each time too.

[meps]

Bump. (Sorry, but I'd really love some help with this )