Applying VBA to any row

December 15, 2023

I’m trying to write a script where if anything in columns F to KA changes, column D of the same row is cleared.

I have the following script:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F7:KA7")) Is Nothing Then
    Range("D7").ClearContents
End If
End Sub

This clears content from a specific cell (column C) if anything in that row changes. I’d like it to apply to any row where it’s always column D that is cleared where anything in F to KA changes.
I though it would be straightforward but can only get it to change D7 where anything in any row changes, instead of column D in the edited row.

>Solution :

Just use the Target to indicate which row(s) you need to change, added a for-loop to do it for multiple rows in case you’re changing more than one cell at once.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, ccell
    Set rng = Intersect(Target, Range("F:KA"))
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        For Each ccell In Intersect(rng, Target.Cells(1, 1).EntireColumn)
            Range("D" & ccell.Row).ClearContents
        Next ccell
        Application.EnableEvents = True
    End If
End Sub