cell colour formatting with multiple arguments

Hi all,

I am creating a spreadsheet with milestones in sheet1 and tasks for these milestones in sheet2.

I would like to create a rule that:

- If all the tasks for a milestone in sheet2 shows TRUE, then change colour of the milestone in sheet1 to GREEN.
- if one or more of the tasks for a milestone in sheet2 shows FALSE, then change the colour of the milestone in sheet1 to AMBER.
- If all the tasks for a milestone in sheet2 shows FALSE, then change the colour of the milestone in sheet1 to RED.

For example, there is a milestone cell called Patch Cables is in sheet1. In sheet2, there are 3 tasks for the Patch Cables milestone. If these tasks all show TRUE, then the colour of the milestone Patch Cables in sheet1 is automatically changed to GREEN. If one or more tasks shows FALSE, then the colour of the milestone changes to AMBER automatically. If all the tasks show FALSE, then the colour of the milestone stays RED as the default colour.

How would I do this?

Thanks

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Is it always 3 tasks per Milestone?
Try attachment.

I tried to put a string ("xxx" or any string) in C18 to mark "End of table"
See attachment.

Try hitting Ctrl-F3 to see define name RowNo

The RowNo defines how many rows of tasks

Then use OFFSET to define the range of task per milestone

Originally Posted by DefenderAtkins

Hi bebo21999,

Thank you very much for your answers. However, I still dont get how the cells would change colour by looking at the =MATCH statement. Could you please kindly give me more info how you did it?

Try to highlight each small part of the combination, then hit F9 to see result, then you can understand how it works.
It is hard to explain unless you understand deeply how MATCH, OFFSET works