Delete cell contents if not today's date in adjacent cells

[kelly mort]

I have a table in the range B4 : N1004 .

Column E contains values and F contains Dates.

And G contains values and H dates etc to N .

I am looking for a script that will look at column F for dates other than today's date. Cells that don't have today's date, clear that cell and the adjacent cell to the left.

So if cell F5 does not contain today's date, then we clear it's content and the content of E5 in that order.

Thanks in advance

 

[kelly mort]

Cool it's working now.


There are no formulas on the sheet.

Thanks

 

[MARK858]

There are no formulas on the sheet.

That wasn't what I asked (it was what did the formula I posted give you) but irrelevant as you have it working now.
As for a range of dates, then the below with clear everything between tomorrow and 10 days after today.

Sub ClearNotTodayV4()
    Dim k As Long
    Application.ScreenUpdating = False
    k = 5
    With ActiveSheet
        .AutoFilterMode = False
        .[B3:N3].AutoFilter 5, ">" & CLng(Date), xlAnd, "<+" & CLng(Date + 10)
        .Cells(4, k).Resize(1000, 2).Value = ""
        .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True
End Sub

 

[cooper645]

How about this, should be able to modify it to suit your own ranges, and whether to include/exclude the dates in the check or not

Option Explicit

Sub a1084411()
'https://www.mrexcel.com/forum/excel-questions/1084411-delete-cell-contents-if-not-todays-date-adjacent-cells.html
Dim r As Integer, c As Integer, LRow As Long
Dim Date1 As Date, Date2 As Date


Application.ScreenUpdating = False


LRow = Cells(Rows.Count, "F").End(xlUp).Row
Date1 = Range("C2").Value
Date2 = Range("D2").Value


For c = 6 To 14 Step 2
    For r = LRow To 5 Step -1
        If Cells(r, c).Value >= Date1 And Cells(r, c).Value <= Date2 Then
            'Do Nothing
        Else
            Range(Cells(r, c - 1), Cells(r, c)).ClearContents
        End If
    Next r
Next c


Application.ScreenUpdating = True


End Sub

 

[MARK858]

Sorry I made a typo in post number 12, it should read...

Sub ClearNotTodayV4()
    Dim k As Long
    Application.ScreenUpdating = False
    k = 5
    With ActiveSheet
        .AutoFilterMode = False
        .[B3:N3].AutoFilter 5, ">" & CLng(Date), xlAnd, "<[COLOR="#FF0000"][B]=[/B][/COLOR]" & CLng(Date + 10)
        .Cells(4, k).Resize(1000, 2).Value = ""
        .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True
End Sub

 

[kelly mort]

Great, both codes are working good .

I love them.