Apply conditional formatting to date, ignoring strikethrough

[jlcm0924]

I'm working on a sheet with a Target date column, however once the target date has arrived / passed there can be a new Target date issued but I need to see that the original target date has been missed. Is there a way to apply conditional formatting using the date that does not have the strikethrough?

 

[Micron]

Don't know if it can be achieved with a formula, but pretty sure it can be done with vba if you can make use of that. I imagine you are using Alt+Enter to add the second date on the next line in the cell? Could there ever be more than 2 dates in one cell?

 

[Micron]

At first I intended to work with the strikethrough font but after a bit of thought I guess it doesn't matter if there are more than 2 dates if the code looks right to left for a linefeed character. So maybe this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str As String
Dim intPos As Integer

If Not Intersect(Target, Range("N:N")) Is Nothing Then 'change to your date column
    str = Target.Value
    intPos = InStrRev(str, Chr(10)) + 1
    If intPos > 0 Then str = Mid(str, intPos) '>0 means there is a line feed character
    If CDate(str) > Date Then
        Target.Interior.ColorIndex = -4142
    Else
        Target.Interior.Color = vbRed
    End If
End If

End Sub

As always, test on a copy of your workbook. Enter cell values and move off of the cell (just hit Enter)?