Conditional formatting= ignore text in cells & how to trigger a macro from cell content

[Guacamoley]

Hello, I am fairly new to VBA and Formulas I am so far stuck with two issues (im using XL2000). Just incase it helps this database is to calculate when training courses are due.

Firstly, I have a worksheet with different dates in I have used this formula =EDATE(D5,12*D4) to calcualte what the dates will be in 5 years time -this works fine. With this result I want the cells that are equal to or more than TODAY to turn red. The formula i have been using so far is =E14<=TODAY() The problem that I am having with this is that some of the fields have text in them instead of dates and so it automatically turns them red when I want them to remain uncoloured. Is there anyway I can get it to ignore text and purely focus on the dates?

The second problem I am having is once this formula is working, how can I then get the cells that are overdue dates to trigger another macro? (This is an Email sent to the user that I have already written) Would I get this to trigger by the colour of the cell or would it have to be done by date?

I hope someone can help me!

 

[nightcrawler23]

In your conditional formatting box
use a formula to determine which text to format
and use this formula

=IF(NOT(ISTEXT($E14)),$E14>=TODAY(),)

 

[Weaver]

The second problem I am having is once this formula is working, how can I then get the cells that are overdue dates to trigger another macro? (This is an Email sent to the user that I have already written) Would I get this to trigger by the colour of the cell or would it have to be done by date?

I hope someone can help me!

You'd need to trigger off the date, since the state of a conditional format is notoriously tricky to detect as a general case in VB. For instance, the interior.colorindex property of the cell will not change. You'd wind up having to extract the condition and then evaluating it separately anyway.

 

[Guacamoley]

Thank you so much for your help guys It is working much better now!

 

[Guacamoley]

In your conditional formatting box
use a formula to determine which text to format
and use this formula

=IF(NOT(ISTEXT($E14)),$E14>=TODAY(),)

I have come across a problem, if I wanted to make result predict 7 days prior to TODAY how would I do this?

Thanks again