VBA worksheet event to delete dependent validation list

[cidfidou]

Hi Excel jedis,

how can I change the below code to only clear a specific cell instead of column . (lets say cell B2 and C2 and clearing C2 when B2 i changed.. )

As usual, thanks inadvance



Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub

 

[Fishboy]

how can I change the below code to only clear a specific cell instead of column . (lets say cell B2 and C2 and clearing C2 when B2 i changed.. )

Isn't that what it is already doing? If target column is B the corresponding cell from column C is cleared.

 

[cidfidou]

Hi fishboy,

this is correct but I am trying in vain to define a specific cell instead of the columns

 

[cidfidou]

hi Guys,

I made it work this way

rivate Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$12" Then
Range("B12") = "ALL"

End If
End Sub

 

[Fishboy]

Private Sub Worksheet_Change(ByVal target As Range)
If target = Range("B2") Then
Worksheets("Sheet1").Range("C2").ClearContents
End If
End Sub