Want to highlight tasks in a listbox that are overdue

[Marty Brittain]

I have an Access form with a listbox that contains a column with DueDate. I want all of the rows that are past the Due Date to be highlighted in vbRed.

The code that I have tried is this:

If Me.cm_todo_lst. Column(3) < Date Then
Me.cm_todo_lst. ForeColor = vbRed
Else
Me.cm_todo_lst. ForeColor = vbBlack
End If

Thanks in advance.

[malcolmk]

why not simple tie the listbox to a query to retrieve ONLY overdue tasks.

[Marty Brittain]

The listbox contains items is a To Do list. Each item has a different duedate. All items in the list must be shown.

[Margie Brown]

Could you try conditional formatting tied to the due date, ie if Due Date is equal to or greater than current date --- highlight due date text in red

[Margie Brown]

Correction that should read conditional formatting applied to the due date field.

[ADezii]

@Marty - I don't think that what you are requesting is possible. What you can do, however, is to 'Select' any Row whose Date Value in Column 3 (4th) is less than the present Date or any Date Value:

Dim lstSource As ListBox
Dim intCurrentRow As Integer
    
Set lstSource = Me![cm_todo_lst]
    
For intCurrentRow = 0 To lstSource.ListCount - 1
  lstSource.Selected(intCurrentRow) = (CDate(lstSource.Column(3, intCurrentRow)) < Date)
Next

[colintis]

ADezii, can you explain a bit on this one?

lstSource.Selected(intCurrentRow) = (CDate(lstSource.Column(3, intCurrentRow)) < Date)

Does this means it will select the item if the criteria is true?

[ADezii]

Does this means it will select the item if the criteria is true?

Yes, if the Value (Date) in Column 3 of the Current Row is < Date, that Row will be selected as well as any others meeting the criteria. Should you need a simple illustration, I can provide one.

[OldBirdman]

If you substitute a sub-form for your listbox, you can apply conditional formatting to any row. You can make the sub-form appear almost identical to a listbox, and the user won't know the difference.

[colintis]

I see, so I can save 2 lines of code by putting the IF statement :D