Conditional cell colour

[Hennie]

Amongst others, my spreadsheet headings include "Product Code" and eac
day of the month. The status of the "Product Code" can change from da
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 t
red.
Should the status initially be "Passed" and later change to "Reject"
the colour must change to orange.
Please refer attachment

+-------------------------------------------------------------------
|Filename: Batch Status Report2.pdf
|Download: http://www.excelforum.com/attachment.php?postid=5050
+-------------------------------------------------------------------

 

[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