Clear cell contents if another is changed for a range of cells in vba

0
votes

I have drop down lists in C8 to FU8 and drop down lists in C9 to FU9 that depend on the value selected in the first drop down list. I'm trying to clear the cell contents of C9 to FU9 if the value in C8 to FU8 is changed. I have managed to get this to work when I specify singular cells e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

For Each Cell In Target
    If Cell.Address = "$C$8" Then
        Application.EnableEvents = False
            Range("C9").ClearContents
        Application.EnableEvents = True
    End If
Next Cell

End Sub

However, I can't get this to work for the whole range of cells. I also want each column to be independent so that if the value in D8 is changed this doesn't clear the contents of all cells in range C9:FU9 and only clears the contents of D9.

Any help would be appreciated thank you!

2
excelvba

2 Answers

1
votes

Your first paragraph makes it sound like you want all values to clear when you update a single value.

Your last paragraph says you want the cell below the one that was changed to clear - this sounds more likely so I'll give the answer to that: 

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    'Check the cell being changed is in the range C8:FU8.
    If Not Intersect(Target, Range("C8:FU8")) Is Nothing Then
        'Clear the cell below the one that was changed.
        Target.Offset(1).ClearContents
    End If

    Application.EnableEvents = True

End Sub
0
votes

Try checking if the changed cell is in the column in question (3 in this example), and then clear the cell that is in the same row but column FU (177).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range

For Each myCell In Target
    If myCell.Column= 3 Then
        Application.EnableEvents = False
            Cells(myCell.Row, 177).ClearContents
        Application.EnableEvents = True
    End If
Next myCell

End Sub

Also, the variable name Cell is confusing to me, I prefer to make it clearer that this is a variable, not an existing Excel object - hence the rename to myCell.