CODEWITHSUNDEEP
vbaexcel
James
James

Reputation: 3

How can I loop through an If/Then Statement

I have a parent drop down box in Column I and a child drop down box in Column J that changes based on Column I.

If column I changes, I would like column j to reset rather than keep its original value.

I have this code working for my first row

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "I2" Then Range("J2").ClearContents
End Sub

How can I get it to loop through every row?

Many Thanks, James

Upvotes: 0

Views: 61

Answers (2)

Comintern
Comintern

Reputation: 22185

I'd check to see if the Target range intersects with column I, then loop through all of the cells in the changed range. You can use the .Row property inside the loop to clear the corresponding cell:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Did something change in column I?
    If Intersect(Target, Me.Range("I:I")) Is Nothing Then
        Exit Sub
    End If

    Dim test As Range
    'Loop through all of the changed cells.
    For Each test In Target
        'If the cell is in column I... 
        If test.Column = 9 Then
            '...clear column J in that row.
            Me.Cells(test.Row, 10).ClearContents
        End If
    Next
End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

Do you mean whenever a value in I is changed you want the corresponding value in J cleared? Better to restrict to a specific range of I/J - this will do the whole column.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.column = 9 Then target.offset(,1).ClearContents
End Sub

Upvotes: 2

Related Questions