Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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. +-------------------------------------------------------------------+ |Filename: Batch Status Report2.pdf | |Download: http://www.excelforum.com/attachment.php?postid=5050 | +-------------------------------------------------------------------+ -- Hennie ------------------------------------------------------------------------ Hennie's Profile: http://www.excelforum.com/member.php...nfo&userid=399 View this thread: http://www.excelforum.com/showthread...hreadid=562610 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Hennie Wrote: 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"),TR UE,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 -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=562610 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() 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 ------------------------------------------------------------------------ Hennie's Profile: http://www.excelforum.com/member.php...nfo&userid=399 View this thread: http://www.excelforum.com/showthread...hreadid=562610 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Anybody that can help perhaps? -- Hennie ------------------------------------------------------------------------ Hennie's Profile: http://www.excelforum.com/member.php...nfo&userid=399 View this thread: http://www.excelforum.com/showthread...hreadid=562610 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Conditional Formatting For A Cell Other Than The One With The Form | Excel Worksheet Functions | |||
Fill colour a cell from the linked cell | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |