Conditional formatting based on value of multiple cells

[pulsar77]

Hello,
I am not sure if this is a VBA/macro problem or not so please excuse me if this is not in the right forum section.

I am trying to change the color of cells on one sheet of a workbook based on multiple values on another sheet. I have made this work for up to 3 cells but get error messages beyond that. Here is what I specifically need

Workbook sheet one contains a list of parts (25 for now, but will increase) that are assembled to make larger items which are listed on sheet 2.

Each part on sheet one has 5 possible states, blank, failed test, passed test, test waived, test passed with exceptions.

Each larger item on sheet 2 depends on the status of the parts on sheet 1 and has 5 possible states, blank, failed test, passed test, test waived, test passed with exceptions

So for example, the status of Item A (sheet 2 ,cell A2) depends on the status of Parts 1-6 (sheet 1, cells A2-A7).
If all 6 parts are blank, Item A background is white
If one or more of the 6 parts are "failed test" Item A background is red.
If all 6 parts are "passed test", item A background is green
If one or more of the 6 parts are any combination of "test waived" or "test passed with exceptions" item A background is yellow.
"failed test" status overrides all others. In other words if one of the 6 parts are "failed test" Item A is red regardless of the status of the other 5 parts.

The code/macro needs to be used for parts groupings of various sizes besides 6. In other words Item B might consist of 15 parts.

I think that describes the entire problem. I would really appreciate help or suggestions. My VBA skills are extremely weak so please don't assume I possess any significant knowledge.

[etaf]

can you post a sample spreadsheet please
on Sheet1 cells A2-A7 - do you also have the corresponding part number in Sheet2
so you can match on partID and then look for those conditions ?
The you maybe able to use a countifs() in a conditional format to count the items