-
Solved: If cell is cleared or blank the conditional formating color is removed
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.
[vba]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[/vba]
-
Hi. I changed/added the red parts. Try:
[vba]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
[/vba]
-
Thanks, this did the trick.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules