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.
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.
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.
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.
