Conditional cell colour

[Hennie]

Amongst others, my spreadsheet headings include "Product Code" and each day of the month. The status of the "Product Code" can change from day to day. At some stage the status will change to "Passed" or "Reject". This change can appear under any date heading.
When the status changes to "Passed", the cell colour of "Product Code" must change to green and when status becomes "Reject" it must change to red.
Should the status initially be "Passed" and later change to "Reject", the colour must change to orange.
Please refer attachment.

[Maistrye]

Amongst others, my spreadsheet headings include "Product Code" and each day of the month. The status of the "Product Code" can change from day to day. At some stage the status will change to "Passed" or "Reject". This change can appear under any date heading.
When the status changes to "Passed", the cell colour of "Product Code" must change to green and when status becomes "Reject" it must change to red.
Should the status initially be "Passed" and later change to "Reject", the colour must change to orange.
Please refer attachment.

Select the entire region you wish to have this sort of formatting apply to and make note of the active cell of the range. (For this, I'm assuming it's F2)

Select Format -> Conditional Formatting ...

Click Add Twice.

For condition 1:
Cell Value Is
equal to
="Passed"

Then, click on the Format... menu and adjust the format for passed.

For condition 2: (remember, assuming F2 is the currently active cell)
Formula Is
=if(and(countif($F2:F2,"Passed")>0,F2="Reject"),TRUE,FALSE)

Then, Format it to be orange.

For condition 3:
Cell Value Is
equal to
="Reject"

The, Format it to be red.

I'm assuming that by "previously passed", you mean before the current day, and that for each item, the status is only on the current line.

If you're looking for a reference on conditional formatting, here's one that may help you:

http://www.contextures.com/tiptech.html

HTH...

Scott

[Hennie]

Scott, this does not work. The status under the date columns do not get changed but only new status for the current date is selected from a Validation List. Example:
03-Jul - status = Under Test
04-Jul - Raw Material Shortage - this could then remain the status for a number of days but the column status for these days will be updated with Raw Material Shortage until a new status becomes applicable. At some stage the product will be approved and the status under that date (for example 17-Jul) will become "Passed". The same applies to "Reject" status. What is also possible is that the product status could change to "Reject" on 23-Jul, hence the need for the change to orange of the cell against the product code in that row.

[Hennie]

Anybody that can help perhaps?