Timestamp Conditional Formating

I'm looking to add some conditional formating to the attahced file. Idealy I would like the formula to take a tamestamp at the time of opening the file and update update all records within the file based on the below statment.

I was wondering if it is possible to this by a nested If statement within Excel function or would I be better off aproching this in VB?

I've attached a sample book and the basic logic for the statement I need is.


Status Case Statement
If B Is not Null and E is Null Then 'Pending'

If E is not null
And 'Now' is Greater then E
And F is null Then 'OverDue'

If F is Less than or Equal to E Then 'Complete'



Fomating Case Statement
If Status is Pending then 'Orange Font'
If Status is Overdue and Complete Date is Null then 'Red Font'