VBA conditional formatting

Hi. I've put the code below in to my form and the ID field formats red, green and yellow fine, however, the time_limit field is purely red. Is it not possible to use dynamic conditional formatting in vba or is there something wrong with my code? Thanks. David.

Private Sub Form_Load()

Dim objFrc As FormatCondition

'delete existing formats
Me.ID.FormatConditions.Delete
Me.TIME_LIMIT.FormatConditions.Delete

'create three format objects and add them to the FormatConditions collection
Set objFrc = Me!ID.FormatConditions.Add(acFieldValue, acLessThan, 4)
Set objFrc = Me!ID.FormatConditions.Add(acFieldValue, acEqual, 4)
Set objFrc = Me!ID.FormatConditions.Add(acFieldValue, acGreaterThan, 4)

'create three format objects and add them to the FormatConditions collection
Set objFrc = Me!TIME_LIMIT.FormatConditions.Add(acFieldValue, acLessThan, DATE)
Set objFrc = Me!TIME_LIMIT.FormatConditions.Add(acFieldValue, acEqual, DATE)
Set objFrc = Me!TIME_LIMIT.FormatConditions.Add(acFieldValue, acGreaterThan, DATE)

'format the time_limit text box with each condition
With Me.ID.FormatConditions(0)
.BackColor = vbGreen
End With

With Me.ID.FormatConditions(1)
.BackColor = vbYellow
End With

With Me.ID.FormatConditions(2)
.BackColor = vbRed
End With

With Me.TIME_LIMIT.FormatConditions(0)
.BackColor = vbGreen
End With

With Me.TIME_LIMIT.FormatConditions(1)
.BackColor = vbYellow
End With

With Me.TIME_LIMIT.FormatConditions(2)
.BackColor = vbRed
End With

End Sub

RE: VBA conditional formatting

Hi Stephen

Thanks for looking into this for me. I've used both on open and on load method but both produce the same results.

In terms of the db; its actually just a test db to test the conditional formatting before I apply it to the main db. The table the form is looking at is 2 columns by 7 rows. Column 1 is the ID (1-7) and column 2 is a date field containing 7 dates that are around DATE, even to the extreme of one at 1974 and one at 2020.

As stated, the ID formatting works on the threshold of 4 in the code but the date is purely red.

Thanks

David

RE: VBA conditional formatting

Hi Stephen

I've had time to play around with this now, it looks like the conditional formatting is based on numbers only so I've done a CLng(DATE) to get the 40240 for today and the date field is now formatting red, yellow and green.

Thanks for your time on this one.

Regards

David