VBA conditional formatting for multiple dates

[Exceldude88]

I am trying to make an excel sheet turn a hole row red when a value in the L column is before the current date, The first code succeeds in only turning the cell in the column red. Additionally, in the k column cell (second code) if the date is less than the L column date I need it to turn the whole row green.

Thank You,



Sub test()

With ThisWorkbook.Worksheets("Sheet1").Range("L:L").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND(L1<>"""",L1<today())"
With .Item(.Count)
.Interior.Color = RGB(255, 0, 0)
.SetFirstPriority
End With
End With
End Sub


Sub test1()

With ThisWorkbook.Worksheets("Sheet1").Range("K:K").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND(K1<>"""",K1<l1())"
With .Item(.Count)
.Interior.Color = RGB(0, 255, 0)
.SetFirstPriority
End With
End With
End Sub

</l1())"
</today())"

 

[JLGWhiz]

Neither of your examples deal with dates and the second code evaluates column K, not column L.
Event code might work better than Conditional Format in this case, because the CF would need to be in each cell of the row to work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Not Intersect(Target, Range("L:L")) Is Nothing Then
    If Date < Target.Value Then
        Target.EntireRow.Interior.Color = vbRed
    Else
        Target.EntireRow.Interior.Color = xlNone
    End If
End If
End Sub

For col K

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Not Intersect(Target, Range("K:K")) Is Nothing Then
    If Date > Target.Value Then
        Target.EntireRow.Interior.Color = vbGreen
    Else
        Target.EntireRow.Interior.Color = xlNone
    End If
End If
End Sub

If your changes in columns L and K are generated by formula, then Worksheet_Calculate would be a better event to use.

 

[Exceldude88]

Thanks a lot for you response! There is no formula. The below code colors the corresponding row red when the L column is less than today's date. I am trying to get rows to color based on 2 separate dates where if L1 (date) < K1 (date) it fills in a color for the row. Why does the second code not work? All it does is make my K column yellow. Please help me to understand?



Sub test()

With ThisWorkbook.Worksheets("Sheet1").Range("A:Z").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND($L1<>"""",$L1<today())"
With .Item(.Count)
.Interior.Color = RGB(255, 0, 0)
.SetFirstPriority
End With
End With
End Sub









Sub test()

With ThisWorkbook.Worksheets("Sheet1").Range("A:Z").FormatConditions
.Add Type:=xlExpression, Formula1:="=AND($L1<>"""",$L1<$K1())"
With .Item(.Count)
.Interior.Color = RGB(255, 0, 0)
.SetFirstPriority
End With
End With
End Sub</today())"

 

[JLGWhiz]

.Add Type:=xlExpression, Formula1:="=AND($L1<>"""",$L1<$K1())"

Don't know. I am not a CF expert. But that looks a lot like a formula that the code is adding into the Conditional Format. I can't see your worksheet so I will take your work for it that the other code worked. Sorry I can't be of more help.
Regards, JLG