CODEWITHSUNDEEP
excelvbadaterange
Antoon
Antoon

Reputation: 107

Excel VBA update the date in a cell if anything changes in a range of cells in the same row

I have a code where the date is updated in the cell in column D if any change was made in the cell in the same row in column F:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code to put the date of the latest update following a change in the corresponding cell in column F
Dim WorkRng As Range
Dim rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("F:F"), Target)
xOffsetColumn = -2 'The date is put 2 columns to the left of column F
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each rng In WorkRng
       If Not VBA.IsEmpty(rng.Value) Then
          rng.Offset(0, xOffsetColumn).Value = Now
          rng.Offset(0, xOffsetColumn).NumberFormat = "dd/mm/yyyy"
       Else
          rng.Offset(0, xOffsetColumn).ClearContents
       End If
   Next
   Application.EnableEvents = True
End If
End Sub

Now I need to adapt this code so that the date is updated in the cell in column D if any change was made in the cells in the same row in columns F to K.

I have very little VBA knowledge and would be grateful for any help in adapting the code.

Upvotes: 1

Views: 3634

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

This appears to work:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code to put the date of the latest update following a change in the corresponding cell in column F
    Dim WorkRng As Range, roww As Long
    Dim rng As Range
    Set WorkRng = Intersect(Range("F:K"), 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, "D").Value = Now
                    Cells(roww, "D").NumberFormat = "dd/mm/yyyy"
                Else
                    Cells(roww, "D").ClearContents
                End If
            Next
        Application.EnableEvents = True
    End If
End Sub

We just do not use OFFSET()

Upvotes: 1

Related Questions