Reset Cell Values Based on Change to Various Cells

[Griffmik]

Good evening. I have a challenging problem I was hoping to get help with. I have reviewed other posts and found a solution that allows for clearing a range of cells based on a change, but can't find a way to clear different ranges based on different cells changing.

I have dependent drop-downs in Cells C2:C5.

I want the cells in C3:C5 to reset to ‘Select’ when C2 is changed
I want the cells in C4:C5 to reset to ‘Select’ when C3 is changed
I want the cell in C5 to reset to ‘Select’ when C4 is changed

I’m using the following statement that resets C3:C5 when C2 is changed, but cannot get the others to work. Any help is greatly appreciated. Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then
Range("C3:C5").Value = "Select"
End If
End Sub

 

[Osvaldo Palmeiro]

Hi. Try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, [C2:C4]) Is Nothing Then Exit Sub
 Select Case Target.Address(0, 0)
  Case "C2": [C3:C5] = "Select"
  Case "C3": [C4:C5] = "Select"
  Case "C4": [C5] = "Select"
 End Select
End Sub

 

[Griffmik]

Thank you Sir!! This works perfectly!!