Excel - Email Marco - Conditional Format Choose Specific Rows of Data to Email

I HAVE DATA BEING KEYED INTO A CUSTOMER RETURN ADVICE (CRA) LOG IN EXCEL THE USER IS TO MANUALLY KEY IN THE DATE GOODS ARE RECEIVED (COLUMN B) THE DUE DATE (COLUMN F) ALREADY HAS A FORMULA IN IT TO CALCULATE 14 DAYS FROM THE DATE THE GOODS ARE RECEIVED (COLUMN B) THE ROWS TO BE CONDITIONALLY FORMATTED - IF THE DUE DATE (COLUMN F) IS IN THE PAST (BEFORE TODAYS DATE) THE ENTIRE ROW IS TO FILL RED AND THE FONT IS TO BE WHITE AND BOLD (REFER TO ROW 6 IN ATTACHED FILE AS EXAMPLE) AND - IF THE DUE DATE (COLUMN F) IS IN THE PAST (BEFORE TODAYS DATE) HOWEVER THE USER HAS MANUALLY KEYED IN A REVISED DATE (COLUMN G) THEN THE ROW STAYS NORMAL AND DOES NOT CHANGE COLOR - (REFER TO ROW 7 IN THE ATTACHED FILE AS AN EXAMPLE) AND IF THE DUE DATE (COLUMN F) IS IN THE PAST (BEFORE TODAYS DATE) AND THE USER HAS MANUALLY KEYED IN A REVISED DATE (COLUMN G) HOWEVER THE REVISED DATE IS NOW ALSO IN THE PAST (BEFORE TODAYS DATE) THE ENTIRE ROW IS TO FILL RED AND THE FONT IS TO BE WHITE AND BOLD (REFER TO ROW 8 IN ATTACHED FILE AS EXAMPLE) AND WHENEVER THE USER HAS MANUALLY KEYED IN A DATE COMPLETE (COLUMN J) THEN NO MATER WHAT DATES ARE IN THE DUE DATE (COLUMN F) AND REVISED DATE (COLUMN G) THE ENTIRE ROW IS TO BE FILLED GREEN WITH BLACK FONT AND BOLD) - REFER TO ROW 9 IN ATTACHED FILE AS AN EXAMPLE) THE CUSTOMER COLUMN (COLUMN C) IS TO HAVE DATA VALIDATION - WITH A DROP DOWN LIST FROM DATA STORED IN COLUMN A FROM THE SHEET TAB TITLED ''CLIENT ALLOCATION' - THIS LIST WILL BE AROUND 2000 CELLS LONG AND NEED TO BE ABLE TO ADD TO IT WHEN THE CUSTOMER HAS BEEN SELECTED THEN THE SALES PERSON (COLUMN D) IS TO AUTO FILL USING VLOOKUP WITH DATA STORED IN COLUMN A AND B FROM THE SHEET TAB TITLED ''CLIENT ALLOCATION' THE REVISED DATE COLUMN (COLUMN H) DUE DATE COLUMN (COLUMN F) AND SALES PERSON COLUMN (COLUMN D)IS TO BE PASSWORD PROTECTED WITH THE PASSWORD IIMSCRA - SO ONLY PARTICULAR USES CAN KEY IN A REVISED DATE THE LAST EMAIL COLUMN (COLUMN L) IS TO HAVE A CHECK BOX (OR SOMETHING SIMILAR) SO THAT IF THE CHECK BOX IS CHECKED THEN THE USER CAN THEN CLICK ON THE 'EMAIL' BUTTON ABOVE (J2) - WHEN THIS EMAIL BUTTON IS CLICKED IT TAKES THE DATA KEYED IN THAT ROW THAT IS CHECKED AND POPULATES THAT DATA INTO THE CORRSPONDING CELLS IN THE SHEET TAB TITLED CRA FORM - THE CRA FORM HAS THE SAME CELL TITLES AS THE CRA LOG SHEET SO THE DATA GOES INTO ITS CORRESPONDING FIELD IN THE CRA FORM SHEET - AND THEN SENDS THAT CRA FORM AS A SEPARATE SPREAD SHEET IN AN EMAIL THE EMAIL ADDRESS TO SEND THE CRA FORM TO IS BASED ON THE EMAIL ADDRESS ASSIGNED TO THE SALES PERSON (COLUMN D) THAT IS STORED IN COLUMN C OF THE SHEET TAB TITLED CLIENT ALLOCATION - I WANT TO BE ABLE TO ADD OTHER EMAIL ADDRESSES INTO OUTLOOK BEFORE THE THE EMAIL SENDS - SO OUTLOOK IS TO OPEN WITH THE SALES PERSONS EMAIL ADDRESS IN IT AND THE CRA FORM ATTACHED BUT I HAVE TO PHYSICALLY CLICK SEND TO SEND THE EMAIL FROM OUTLOOK