Search for text in cell and then run if/then statement on adjacent cell-CodePudding

I am trying to write a macro that looks for a specific word ("Yes") in Column A and then if it is there then to look in column D (cell.Offset(0, 4)). If the date value of the cell is between today (Now) and Two years, then fill in the box. The problem I'm having is I can't seem to tie the two searches together. I can find all the cells that contain "Yes" and I can then go to the adjacent cell in column D. I just can't seem to get the next portion to run.

The end result would look something like the picture:

Sub Test_Text()
    Range("A7").Value = Now
    Dim cell As Range
    For Each cell In Range("$A1:$A6")
    If InStr(cell.Value, "YES") > 0 Then cell.Offset(0, 4).Activate 
    'this activates cells in column D
    'can't seem to now run the if/then statement on activated cell
    If ActiveCell.Value > DateAdd("d", 1, Now) And (cell.Value) < DateAdd("d", 730, Now) Then cell.Interior.Color = RGB(0, 176, 80)
    Next cell
    Range("A7").Select
    Selection.ClearContents
End Sub

CodePudding user response：

see comments:

Sub Test_Text()
    With ActiveSheet 'better to set the specific sheet ie Worksheet("Sheet1")
        .Range("A7").Value = Now
        
        Dim cell As Range
        For Each cell In .Range("A1:A6") 'No need for `$` in vba
            If UCase(cell) = "YES" Then 'case insensitve
                With cell.Offset(, 3) 'we now refer to the cell in column D.
                    If .Value > DateAdd("d", 1, Now) And .Value < DateAdd("d", 730, Now) Then .Interior.Color = RGB(0, 176, 80)
                End With
            End If
        Next cell
        .Range("A7").ClearContents
    End With
End Sub