How to conditionally add / update dates on row update in Excel using Visual Basic

Question

My data is in an Excel table where the users are able to manipulate the data in rows A through S.

I am trying to figure out a way to accomplish the following tasks:

• Populate the cell in Column P with today’s date when a new row is created
• Populate the cell in Column U with the date that the last update was made to any cell between columns A through S in the row

When data in column D is updated, I need other columns to populate a date depending on the value in column D:

• If D is updated to “Approved”, then today’s date should be populated in Column R
• If D is updated to “Funded”, then today’s date should be populated in Column S
• Etc

I have been looking up examples of code, but being new to Visual Basic I am having a hard time finding examples of each of these, especially the “created” and “status update” one, and im having a tough time adapting what I do find to my scenario.

Any help would be appreciated!

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng As Range, roww As Long
    Dim rng As Range
    Set WorkRng = Intersect(Range("A:S"), Target)
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
            For Each rng In WorkRng
                roww = rng.Row
                If Not rng.Value = "" Then
                    Cells(roww, "U").Value = Now
                    Cells(roww, "U").NumberFormat = "mm/dd/yyyy"
                End If
                If ActiveCell.Column = "D" And rng.Value = "Approved" Then
                    Cells(roww, "R").Value = Now
                    Cells(roww, "R").NumberFormat = "mm/dd/yyyy"
                End If
            Next
        Application.EnableEvents = True
    End If
End Sub

Answer 1

I have no idea if this is the best way to do it, but as far as I can tell this is working to accomplish what I was trying to do. I'm open to suggestions on how to improve it!!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WorkRng As Range, StatusRng As Range, roww As Long, Srow As Long
    Dim rng As Range, Srng As Range
    Set WorkRng = Intersect(Range("A:S"), Target)
    If Not WorkRng Is Nothing Then
        Application.EnableEvents = False
            For Each rng In WorkRng
                roww = rng.Row
                If Not rng.Value = "" Then
                    Cells(roww, "U").Value = Now
                    Cells(roww, "U").NumberFormat = "mm/dd/yyyy"
                End If
                If rng.Column = Range("D:D").Column And rng.Value = "Approved" Then
                    Cells(roww, "R").Value = Now
                    Cells(roww, "R").NumberFormat = "mm/dd/yyyy"
                End If
                If rng.Column = Range("D:D").Column And rng.Value = "Approved w/ Conditions" Then
                    Cells(roww, "Q").Value = Now
                    Cells(roww, "Q").NumberFormat = "mm/dd/yyyy"
                End If
                If rng.Column = Range("D:D").Column And rng.Value = "Funded" Then
                    Cells(roww, "S").Value = Now
                    Cells(roww, "S").NumberFormat = "mm/dd/yyyy"
                End If
                If Not rng.Value = "" And Cells(roww, "P").Value = "" Then
                    Cells(roww, "P").Value = Now
                    Cells(roww, "P").NumberFormat = "mm/dd/yyyy"
                End If
            Next
        Application.EnableEvents = True
    End If
End Sub