VBA, Highlight If Text AND Dated After Specific Year

[NEW_2VBA]

Hello!
I used the following code to highlight an entire row if column F contained specific text, but I'm now struggling with how to also specify that it must be dated after 1/1/2013. So I'm looking to include those two conditions without using conditional formatting.

Any feedback is greatly appreciated. Thank YOU!!! ?

Dim Cell As Range, Addr As String
With Range("F1", Cells(Rows.Count, "F").End(xlUp))
Set Cell = .Find("*Unit Costs*", , xlValues, , , , False, , False)
If Not Cell Is Nothing Then
Addr = Cell.Address
Do
Cell.EntireRow.Interior.Color = RGB(230, 184, 183)
Set Cell = .FindNext(Cell)
Loop While Not Cell Is Nothing And Cell.Address <> Addr
End If

 

[NEW_2VBA]

Forgot to add that the Text is in Column F, and the Date is in Column I

 

[JoeMo]

Try:

Sub test()
Dim Cell As Range, Addr As String
With Range("F1", Cells(Rows.Count, "F").End(xlUp))
    Set Cell = .Find("*Unit Costs*", , xlValues, , , , False, , False)
    If Not Cell Is Nothing Then
        Addr = Cell.Address
    End If
    Do
    If Cell.Offset(0, 3).Value > #1/1/2013# Then
        Cell.EntireRow.Interior.Color = RGB(230, 184, 183)
    End If
        Set Cell = .FindNext(Cell)
        If Cell Is Nothing Then Exit Do
        If Cell.Address = Addr Then Exit Do
    Loop
End With
End Sub

 

[NEW_2VBA]

Thanks so much! It worked! ?

 

[JoeMo]

Thanks so much! It worked! ?

You are welcome - thanks for the reply.

 

[NEW_2VBA]

Sorry, another text entry was requested to be included.
I amended the .Find portion of the code as follows to include both but that doesn't seem to work. I appreciate any feedback, thanks!

Set Cell = .Find("*MILESTONE*,*Unit Costs*", , xlValues, , , , False, , False)

 

[JoeMo]

Do you expect there are cells that contain both sub-strings or what? You need to tell us what exactly you want to do.

 

[NEW_2VBA]

Cells will either contain one or the other sub-strings, so I would need to highlight rows with text "Unit Costs" or "Milestone" dated 1/1/2012+.

 

[JoeMo]

This will highlight rows with col F cells containing "unit costs" and a date greater than 1/1/2013 in col I, or col F cells containing "milestone" and a date greater than 1/1/2012 in col I.

Sub test()
Dim Cell As Range, Addr As String, Strs(1 To 2), Dts(1 To 2)
Strs(1) = "*Unit Costs*": Strs(2) = "*MILESTONE*"
Dts(1) = #1/1/2013#: Dts(2) = #1/1/2012#
For i = 1 To 2
    With Range("F1", Cells(Rows.Count, "F").End(xlUp))
        Set Cell = .Find(Strs(i), , xlValues, , , , False, , False)
        If Not Cell Is Nothing Then
            Addr = Cell.Address
        End If
        Do
        If Cell.Offset(0, 3).Value > Dts(i) Then
            Cell.EntireRow.Interior.Color = RGB(230, 184, 183)
        End If
            Set Cell = .FindNext(Cell)
            If Cell Is Nothing Then Exit Do
            If Cell.Address = Addr Then Exit Do
        Loop
    End With
Next i
End Sub

 

[NEW_2VBA]

I can't say thank you enough for your help! It worked! ? ?

I noticed however that the spreadsheet must contain both text strings. That how I got it to work.
The spreadsheet will either have: 1) Both text strings
2) One or the other
3) Neither