RonNCmale
02-22-2013, 02:48 AM
I have the following code in a userform and works well, but when dates are equal to or less than today's date it will color the cell a particular color. I need to be able to delete the date and the cell go back to a blank cell with no color until another date is entered and depending on whether the date is equal to or less than today's date it will color the cell. Any help would be appreciated.
Private Sub UserForm_Initialize()
Dim rDates As Range
With Sheet1
Set rDates = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With
rDates.Interior.ColorIndex = xlNone
For Each cl In rDates
Select Case cl.Value
Case Is = Date
Me.ListBox1.AddItem cl.Offset(0, -1).Value
cl.Interior.ColorIndex = 6
Case Is < Date
Me.ListBox2.AddItem cl.Offset(0, -1).Value
cl.Interior.ColorIndex = 7
End Select
Next cl
End Sub
Private Sub UserForm_Initialize()
Dim rDates As Range
With Sheet1
Set rDates = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With
rDates.Interior.ColorIndex = xlNone
For Each cl In rDates
Select Case cl.Value
Case Is = Date
Me.ListBox1.AddItem cl.Offset(0, -1).Value
cl.Interior.ColorIndex = 6
Case Is < Date
Me.ListBox2.AddItem cl.Offset(0, -1).Value
cl.Interior.ColorIndex = 7
End Select
Next cl
End Sub