Excel 2003 VBA coding conditional formatting based date test

[wbow]

Hi,
I have an excel spreadsheet which monitors the progress of new parts through our business.

I have been tasked with setting up a traffic light system to flag up the status of each of the stages, with each row of the sheet covering one part, each column covering each stage of the process.

The stages set as column headers are either target dates[tgt], promised dated [pr] or stage completion dates as follows;
Part_No
RFQ
Order_Recd
Cust_Dely_Date
Contract_Review_Tgt
Contract_Review
CD_Drwg_Tgt
CD_Drwg_Pr
CD_Drwg_Fin
Tooling_Drwg_Tgt
Tooling_Drwg_Pr
Tooling_Drwg_Fin
Raw_Material_Stk_Tgt
Raw_Material_Stk_Pr
Raw_Material_Stk_Y/N
Tooling_Order_Tgt
Tooling_Order_Pr
Tooling_Delvd_Y/N
Bought-In_Items_Tgt
Bought-In_Items_Pr
Bought-In_Items_Fin_Y/N
Production_Tgt
Production_Pr
Production_Fin
HT_Tgt
HT_Pr
HT_Fin
Finish_Tgt
Finish_Pr
Finish_Fin
QA_Tgt
QA_Pr
QA_Fin_Y/N

The issue I have is that each of the stages has a standard date taken from either the Contract_Review date or a preceding operation’s date which should be before the Cust_Dely_Date. A Planned date and an actual delivery date.

TargetDateIntervals Days Calculate
Contract Review 3 From Receipt of Order 3
CD and Tooling Drawings 10 From Contract Review 13
Raw Material Order 3 From CD and Tooling Drawings 18
Raw Material Dely 15 From Raw Material Order
Tooling Order 3 From CD and Tooling Drawings
Tooling Dely 15 From Tooling Order
Production 5 From Receipt of RM or Tooling Whichever is later 5
H/T 5 From Finished Production 5
Plate_Patch 5 From Finished H/T 5
PPAP 10 From Completed Production 10
TargetCompletionDate 60 From Contract Review Acceptance 60


I have tried using conditional formatting but this wasn’t flexible enough to handle the three optional test dates and Green, Amber, Red cell background colourings.

My aim is as follows;
First test if the Cust_Dely_Date is less than the Target Completion Date, if so turn the Cust_Dely_Date cell BLUE.
If the Cust_Dely_Date is less than the Target Completion Date, then base all date calculations on the Cust_Dely_Date to generate the Target Dates.

Second test; find all cells without a promised [aaa_Pr] date or a date greater than the target date and colour the cell RED.

Third test, find all cells with a promised [aaa_Pr] date less than the target date and less than or equal to the next process interval days and colour the cell Amber.
Eg. Tooling delivery is 27/02/12 and production is = Workday (27/02/12,5) or Raw Material Dely +5, whichever is greater.

Finally; where there is a delivery or finished date entered, turn the range of cells relating to that operation Green. And if the QA_Fin_Y/N cell is “Y”, turn the entire row GREEN.

Apologies if this is in the wrong forum, any help will be appreciated.

 

[SkipVought]

hi,

Please post a small sample of data, with columns for part-no and 2 sets of dates, like CD_Drwg & Tooling_Drwg, complete with dates and sufficient rows that represent EACH condition that you specify, that can be used for testing, along with a statement of the CF results you expect.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wbow]

Hi SkipVought, I've uploaded a sample of the Excel sheet with comments appended to cells to show the CF outcome required.

Many thanks for your help.

 

[SkipVought]

Many of us are restricted by company security, from downloading files.

Please post your examples HERE, using TGML tags. If you do not know what TGML tags are, then SEARCH ofr TGML on this web page and follow the link.

The TT tag will make it possible to LINE UP YOUR COLUMNS of data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wbow]

Hi SkipVought,
Below is a very reduced version of the layout as requested. Tried to utilise TGML but apologies if not adequate.

CD_No Order_Recd Cust_Dely_Date Contract_Review_Tgt Contract_Review CD_Drwg_Tgt CD_Drwg_Pr CD_Drwg_Fin Rep_Date
12345 01/02/2012[Blue]28/02/2012[/Blue]06/02/2012 [Green]01/02/2012[/Green] 15/02/2012 [Red]20/02/2012[/Red]
12346 28/02/2012 05/05/2012 02/03/2012 24/02/2012 [Green]09/03/2012 12/03/2012 10/03/12[/Green] 05/05/12

 

[SkipVought]

I do not think that you posted...

1) sufficient rows that represent EACH condition that you specify, that can be used for testing, along with

2) a statement of the CF results you expect.

Emphasis added.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wbow]

SkipVought,
I hope this explains things. I have other columns, but once I have the gist of how the code works, I should be able to complete the rest myself.

CD_No RfqNo ItemNo Customer PartNo Order_Recd TargetDueDate Cust_Dely_Date Contract_Review_Tgt Contract_Review CD_Drwg_Tgt CD_Drwg_Pr CD_Drwg_Fin Production_Tgt Production_Pr Production_Fin QA_Tgt
999999 99-9999 99 ZZ 123456 05/03/2012 28/05/2012 20/04/2012[/color blue] 08/03/2012 08/03/12[/color green] 13/02/2012 20/02/12[/color yellow] 13/01/1900 27/02/12[/color red] 20/04/2012[/color blue]

QA-Tgt; Calculated as contract review date + 60 working days. If this date > Cust_Dely_Date then colour Blue and populate field with Cust_Dely_Date.
From this filed's date, all other “Tgt” dates are calculated as follows;
Contract Review 3 From Receipt of Order
CD_Drwg_Tgt 10 From Contract Review
Production_Tgt 5 From Receipt of RM or Tooling Whichever is later [columns omitted for clarity]
QA_Tgt 60 From Contract Review Acceptance





Cust_Del_Date; AS this is less than the TargetDueDate colour is Blue

Contract_Review-Target and Contract_Review; as both are now populated colour is Green for both
If Contract_Review-Target was greater than TargetDueDate or Cust_Dely_Date [whichever is earlier] colour Contract_Review-Target as Red.

CD-Drwg_Tgt; Colour white as CD_Drwg_Fin not populated.

CD_Drwg_Pr; Colour Yellow as date is CD-Drwg_Tgt + 5 working days, <= CD-Drwg_Tgt colour amber, if > CD-Drwg_Tgt+ 5 working days colour red.
Once CD-Drwg-Fin populated, CD-Drwg_Tgt, CD_Drwg_Pr and CD-Drwg-Fin colour green.

Production_Tgt; Colour white as Production_Fin not populated.

Production_Pr; as date is > Production_Tgt + 5 working days colour red. If date = Production_Tgt + 5 working days colour yellow.