Excel Macro to clear cells after 4 months AND when another cell is filled in

[karinfromsweden]

Hi all,

I'm working with a list where I need to automatically clear data in column G-I if two criteria are true:
1) If column N has a value
AND
2) If column A is older than 4 months

I've been googling and reading up, and found something that I think might be on the right track, using the If DateDiff code, but I have no idea how to adjust the code to be matching per row and also take into account that column N needs to be filled in.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Workbook_Open[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Not[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Intersect[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Target[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"N6:N2000"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]))[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Is[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]Nothing[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

  [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] DateDiff[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] FileDateTime[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FullName[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Now[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]>=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]120[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
  [/FONT][/COLOR][COLOR=#858C93][FONT=inherit]' this above needs to only check on cell at the time in A??? [/FONT][/COLOR][COLOR=#303336][FONT=inherit]

        Target[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Offset[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit]-[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]5[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ClearContents
        Target[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Offset[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit]-[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]6[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ClearContents
        Target[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Offset[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]0[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit]-[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]7[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]ClearContents

    ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Save
  [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub

[/FONT][/COLOR]</code>

Am I getting close?

 

[Yongle]

Test in a copy of your workbook

Paste into a standard module

Sub ClearCells()
Dim Cel As Range, Ws As Worksheet
Set Ws = Sheets("[COLOR=#ff0000]NameOfSheet[/COLOR]")
For Each Cel In Ws.Range("A6", Ws.Range("A" & Rows.Count).End(xlUp))
    If DateDiff("d", Cel, Date) > 120 And Cel.Offset(, 13) <> "" Then Cel.Offset(, 6).Resize(, 3).ClearContents
Next Cel
End Sub

If you want to trigger it EVERY TIME the workbook opens then
PLace this in ThisWorkbook module

Private Sub Workbook_Open()
   Call ClearCells
End Sub

 

[ravishankar]

try

Private Sub Workbook_Open()
Dim a As Long
MsgBox "Good Morning"
  For a = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If DateDiff("d", Cells(a, 1), Now()) >= 120 Then
      If Cells(a, "N") <> "" Then
      Cells(a, 1).Interior.ColorIndex = 3 'change 3 to get diff. color
      Range("G" & a & ":I" & a).ClearContents
      End If
    End If
  Next a
 ThisWorkbook.Save
 MsgBox "completed"
End Sub

ravi shankar

 

[karinfromsweden]

Excellent, this works like a charm. Thank you!

Test in a copy of your workbook

Paste into a standard module

Sub ClearCells()
Dim Cel As Range, Ws As Worksheet
Set Ws = Sheets("[COLOR=#ff0000]NameOfSheet[/COLOR]")
For Each Cel In Ws.Range("A6", Ws.Range("A" & Rows.Count).End(xlUp))
    If DateDiff("d", Cel, Date) > 120 And Cel.Offset(, 13) <> "" Then Cel.Offset(, 6).Resize(, 3).ClearContents
Next Cel
End Sub

If you want to trigger it EVERY TIME the workbook opens then
PLace this in ThisWorkbook module

Private Sub Workbook_Open()
   Call ClearCells
End Sub

 

[Yongle]

Thanks for the feedback