Excel VBA - Compare Date and do the following if greater than

[MHamid]

Hello,

I have the below code that is not doing what I need. It is clearing theadjacent data even if the date is less than the look-up date.

I need the code to find all "Completed" statuses in column C, thencompare the date in column D. If the date is greater than the date in cellT1, then clear the data from cells D thru F in that row and replacethe word "Completed" with "In progress".

'Add prior month date, then compare to Publication Date, and Clear data for all "Completed" in columns D, E & F
Dim ddate As Date
Dim rCell As Range
Dim r As Long
    If IsDate(Range("T1")) Then
        ddate = Range("T1").Value
    Else
        MsgBox "Non valid date"
        Exit Sub
    End If
    With Sheets("Report1")
      For Each rCell In .Range(.Cells(1, "D"), .Cells(.Rows.Count, "D").End(xlUp))
        If IsDate(rCell) Then
           If rCell >= ddate Then
                Application.ScreenUpdating = False
                lr = Cells(Rows.Count, "G").End(xlUp).Row
                    For r = 1 To lr
                        If Cells(r, "C") = "Completed" Then
                            Range(Cells(r, "D"), Cells(r, "F")).ClearContents
                            Selection.Replace What:="Completed", Replacement:="In Progress", LookAt:= _
                                xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                                ReplaceFormat:=False
                        End If
                    Next r
                Application.ScreenUpdating = True
            End If
        End If
      Next rCell
    End With

What am I doing wrong?

Thank you

 

[Fluff]

Try

   With Sheets("Report1")
      For Each rCell In .Range(.Cells(1, "D"), .Cells(.Rows.Count, "D").End(xlUp))
         If IsDate(rCell) Then
            If rCell >= ddate Then
               If rCell.Offset(, -1).Value = "Completed" Then
                  rCell.Offset(, -1).Value = "InProgress"
                  rCell.Resize(, 3).ClearContents
               End If
            End If
         End If
      Next rCell
   End With

 

[MHamid]

Awesome!!!! This worked perfectly!!

Thank you

 

[Fluff]

Glad to help & thanks for the feedback

 

[MHamid]

Hey,

Anyway you can check out my new post "Excel VBA - Extracting Data from Another File " and provide some feedback?

Thank you