PDA

View Full Version : Solved: If cell is cleared or blank the conditional formating color is removed



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

omp001
02-24-2013, 09:50 AM
Hi. I changed/added the red parts. Try:
Private Sub UserForm_Initialize()
Dim rDates As Range, cl As Range
With Sheet1
Set rDates = .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp))
End With
Columns(2).Interior.ColorIndex = xlNone
For Each cl In rDates
Select Case cl.Value
Case Is = ""
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

RonNCmale
02-24-2013, 10:35 AM
Thanks, this did the trick.:bow: