CODEWITHSUNDEEP
vbaexcel
Nicholas F.
Nicholas F.

Reputation: 11

Clear contents of one cell when another is changed - for multiple cells

I have searched for an answer on this and cannot find anything specific to my needs. I am creating a sheet that will have multiple rows that need to have the same functionality. For example, in Row 2 - I need the contents of D2:F2 to clear if the value in B2 is changed.

I found a VBA code that works great for that one instance. However, I also need that same functionality in Row 6, Row 10, Row 14, etc. - every 4th row until row 118 - 30 in total.

Each row needs to be exclusive. If I change the value in B2, I don't want it to clear out the values in other rows - only row 2. And if I change the value in B6, I only want the values to change in row 6...and so on.

This is the code I am using for B2, but I wasn't sure if it can be modified for more rows or if I need an entirely different code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Range("D2:F2").ClearContents
    End If
End Sub

Please advise - Thank you!

Upvotes: 1

Views: 180

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

First limit the Target.Row between 2 and 118.

Second, use Mod to check only 4th row.

Try something like the code below:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row >= 2 And Target.Row <= 118 Then
    If (Target.Row + 2) Mod 4 = 0 Then
        If Target.Column = 2 Then
            Target.Offset(, 2).Resize(1, 3).ClearContents
        End If
    End If
End If

End Sub

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

You could use MOD:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 118 Then Exit Sub
    If Target.Row Mod 4 = 2 And Not Intersect(Target, Cells(Target.Row, 2)) Is Nothing Then
        Range("D" & Target.Row & ":F" & Target.Row).ClearContents
    End If
End Sub

Upvotes: 1

Related Questions