VBA Excel Loop with Incremental Rows and Columns-CodePudding

So I'm very new with working with excel's VBA code, and I'm trying to create a 'Date Modified' column for when a value in the previous column was edited for a checklist at work. I've done this once before for another checklist, but I did it the old-fashioned way since it was not a very long checklist. But for this application, that is not efficient at all since this list will be ongoing. I've cobbled together this code using other examples from people in the community, but I can't figure out where the source of the error is coming from. It's saying that there is a compile error 'Do without Loop'. From my understanding from other posts, it thinks that the 'If' statement is not being closed, but I have used an 'End If' and there is only one 'If' statement in my code. I need it to be alternating columns from the 6th column onward and then repeating every row. Any help is much appreciated!

Sub Worksheet_Change(ByVal Target As Range)
Dim ColCount As Long
    ColCount = 6
Dim RowCount As Long
    RowCount = 2
Dim iCol As Long
    iCol = 7
Dim iRow As Long
    iRow = 2
Do While RowCount < 2
    Do While ColCount < 6
        Do While iCol < 7
            Do While iRow < 2
            
                If Target.Column = ColCount And Target.Row = RowCount Then
                    ActiveSheet.Cells(iRow, iCol).Value = Format(Date, "mm/dd/yyyy")
                End If
                
                RowCount = RowCount   1
                ColCount = ColCount   2
                iCol = iCol   2
                iRow = iRow   1

Loop

End Sub

CodePudding user response：

Simpler approach:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range, c As Range
    
    Set rng = Application.Intersect(Target, Me.Range("F:F,H:H,J:J")) 'adjust to suit...
    If rng Is Nothing Then Exit Sub 'no updates in monitored range
    
    For Each c In rng.Cells
        c.Offset(0, 1).Value = Format(Date, "mm/dd/yyyy")
    Next c

End Sub