Conditional Formatting Macro

Hello there,

I am working on a worksheet having 4 segments and each segment has 5 columns in it which are amount received, details requested date, reminder 1, reminder 2, details received . I want a macro that changes the color of these 5 cells (not entire row) based on;

1. The date is put in details requested date column. (Light green color)
2. 3 days passed after details requested i.e reminder 1 due and details received column is blank. (Yellow)
3. Reminder 1 is sent - date of reminder 1 sent (Orange)
4. 3 days passed of reminder 1 sent and details received column is blank. (Pink)
5. Reminder 2 is sent - date of reminder 2 sent.(Red)
6. The color of these cells go white anytime the date is put in details received column.

I would really appreciate your help.

Best Regards

Asif

Dear 6StringJazzer and Shirleyxls,

Thank you so much for your replies. Below is the detail describing data the conditional formatting I want (whether as macro or simply conditional formatting). I am really sorry I can not upload any sheet from the system (access is denied to upload). I would really appreciate your help.

Best Regards

Asif

Client Name Amount Date Payment Details Requested
Date Reminder 1 Reminder 2 Details Received Requirements
20,000.00 10/05/2016 12/05/2016 16/05/2016 - 18/05/2016 The row color should remain white as details are received as mentioned in Column I.
10,000.00 12/05/2016 14/05/2016 18/05/2016 25/05/2016 - The row color should turn red as despite remiders details not received (No date in Column I)
5,000.00 16/05/2016 18/05/2016 22/05/2016 - - The row color should turn orange as Remider 2 is due (date when 1st Remider sent + 3 days lapse )
2,500.00 20/05/2016 22/05/2016 26/05/2016 - - The row color should turn Dark Green as Reminder 1 is sent and Details not received
18,000.00 22/05/2016 24/05/2016 - - - The row color should turn Light green as Reminder 1 is due ( date when details requested + 3 days lapse) and Details not received as per Column I


22,000.00 24/05/2016 28/05/2016 30/05/2016 The row color should remain white as details received within due time.

Oh sorry, I think its messed up. Its not showing the post properly. I would upload the sheet when I get access to my personal pc. I hope you would tolerate it.

Best Regards

Asif

Please find attached the sheet with all descriptions. just want to highlight the row or cells containing the data only.
Dear shirleyxls, I also have tried to do conditional formatting using your formula but it only takes 3 conditions while i want 5 conditions for the same set of cells so it overrides the others.

Best Regards
Asif

Dear shirleyxls,

No I am using Microsoft Office 2013 (Professional Plus). I think the file is saved in 2003 format. But I think VBA macro would be a good option as conditional formatting is not allowing me to use more than 3 conditions. Thanks for your support.

Best Regards

Asif Shabbir

Originally Posted by AsifShabbir

No I am using Microsoft Office 2013 (Professional Plus). I think the file is saved in 2003 format.

The file is saved in the format for Excel 2007 and later. However, your forum profile says you are using Excel 2003, so we thought you were just converting the file to .xlsx format from .xls. If you are using 2013 please update your forum profile so we don't spin our wheels.

Dear shirleyxls,

It is working perfectly. Thank you so much for your help. It really eased my task.

Best Regards

Asif