Datasheet View Form - Conditional Formatting - Choosing between two conditions

[stildawn]

Hi All

I have a form in Datasheet view.

I have two conditional formatting conditions I would like to use on all the records in the form.



First is
Expression: [Added TimeStamp]>Date() - Color in text as BLUE

Second is
Expression: [Expected Arrival Date]<Date()+7 - Color in text as RED


The thing is, that both these conditions can be true at the same time, what I would ideally like to do is use a If statement or something like in VBA it would look something like this as an idea:

If [Expected Arrival Date]<Date()+7 = True
    Color text as Red
Else
    If [Added TimeStamp]>Date() = True
        Color text as Blue
    Else
        Leave color as default
End If

So, the second expression takes priority so all EAD<Date()+7 will be RED, if not then it might be BLUE if AddedTime>Date(), if both are not true then it will be left as default.

Any ideas on how to do this?

Thanks

[stildawn]

Sorry needed to rewrite the second expression is now this:

IIf([Transhipment]=-1,[Expected Arrival Date]-Date()<8)

So it checks if a field =-1, if it does then it does then it finds if the difference between EAD and current date is less that 8. This needs to return RED, and if not then it should check for the BLUE condition?



Alternatively, does anyone know how to run a Background Color conditional formatting on a Yes/No field? The Conditional Formatting open greys out when I select the "Transhipment" field which is a Yes/No field (with tickbox). If I could change the fields background color to RED for when the above expression is true, then I can just leave all the other conditional formatting doing the BLUE operation.