highlight in red if over 3 days excluding weekend

**unley** · 07-20-2010, 08:28 PM

I found some duedate codes in this site but not for this one.

if date in column I is over 3 working days from date on column B in same row, would like the cell in Column I to be highlighted in red but those 3 working days (week days) are not including weekend

Hope this make sense and have attached the dummy workbook for sample

Thank you in advance.

Illegal Dumping Register sample.xls

**Leith Ross** · 07-20-2010, 09:42 PM

Hello Unley,

This would be highlighted in red if the follow up date is more than 3 day (less weekends) from the discovery date?

**unley** · 07-20-2010, 09:48 PM

Yes that's right

**Leith Ross** · 07-21-2010, 12:13 AM

Hello Unley,

I added 2 macros to the attached workbook. One is in the Worksheet_Change event module and the other is in module 1. Here is the code...

Worksheet_Change() Event Code

Please Login or Register  to view this content.

Module1 Code

Please Login or Register  to view this content.

**unley** · 07-21-2010, 12:26 AM

Thank you Leith Ross for your time but when I increased the date in Column I and saved the workbook, the highlight did'nt seem to work when I reopen the workbook

Illegal Dumping.xls

**ajaykgarg** · 07-21-2010, 07:02 AM

Maybe I am missing something, but is a macro required here?
Can't you just use conditional formatting in cell I4 with following condition?

Please Login or Register  to view this content.

**Leith Ross** · 07-21-2010, 12:22 PM

Hello Unley,

I couldn't troubleshoot the problem till this morning. It seems the code must all reside in the Worksheet_Change event. Calling the Sub from the event procedure doesn't work consistently. I moved all the code into the event procedure and it works. Her is the updated macro and workbook.

Worksheet Event Macro Code

Please Login or Register  to view this content.

**unley** · 07-21-2010, 05:42 PM

Thank you very much Leith Ross for this and for your time, it works.

Ajaykgarg, your formula works if another column is inserted for the code to work but the person who uses this workbook don't want any columns changed but thank you too

**ajaykgarg** · 07-21-2010, 11:34 PM

Unley, I don't see the need to insert a new column for the code (conditional formatting) to work. You can just apply this conditional formatting on cells in column I.