How to update the date in Excel column based on date in another column in VBA?-CodePudding

I am writing vba code that is going to update several columns based on user input in a specific column

below is what I have:

If Not Intersect(Target, Me.Columns(46)) Is Nothing Then
If Target.Cells(1).Value = "Scheduled Audit" Then
   Me.Cells(Target.Row, "AU").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
   Me.Cells(Target.Row, "AV").Value = "Issue Audit Agenda"
   Me.Cells(Target.Row, "AW").Value = Format(Date.Column(13) - 30, "mm/dd/yyyy HH:mm:ss")
End If
End Sub

The problem i'm having is i'm not sure how to format the one specific part Me.Cells(Target.Row, "AW").Value = Format(Date.Column(13) - 30, "mm/dd/yyyy HH:mm:ss") because what i'm trying to do is that column AW is going to be the date in another specific column "M"(column 13) minus 30 days. I'm getting the syntax wrong and i'm not sure how to write this out.

CodePudding user response：

Using Cells:

Me.Cells(Target.Row, "AW").Value = Format(Me.Cells(Target.Row, "M") - 30, "mm/dd/yyyy HH:mm:ss")

It might be cleaner to use a With statement:

If Not Intersect(Target, Me.Columns(46)) Is Nothing Then
   With Target
       If .Cells(1).Value = "Scheduled Audit" Then
           Me.Cells(.Row, "AU").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
           Me.Cells(.Row, "AV").Value = "Issue Audit Agenda"
           Me.Cells(.Row, "AW").Value = Format(Me.Cells(.Row, "M") - 30, "mm/dd/yyyy HH:mm:ss")
       End If
   End With
End If