How to clear a cell based on a change in a different cell

[DrJ_Thesising_101]

Hi All

I have created a dependent drop down list where e.g. A3 is Fruit, B3's drop down list = apple, banana, kiwi etc. And if the user changes A3 to Veggies, B3's drop down list = carrot, peas, etc.

If the B3 already has a value in it and the user changes A3, I want B3 to be cleared. I've used the following formula (VB) to do just that, but the problem is it is fixed on row 3.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
Range("B3").ClearContents
End If
End Sub

So, how do I update the formula to say that If A3 changes, then B3 clears. If A4 changes, then B4 clears. If A5 changes then B5 clears, etc. I have a 1000 rows in my sheet that needs to work like this...

[#2]

@DrJ_Thesising_101 

Like this. Adjust the range as needed.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A3:A1000"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Intersect(Range("A3:A1000"), Target).Offset(0, 1).ClearContents
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

[#3]

@DrJ_Thesising_101 

Untested

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    
    Set rng = Intersect(Target, Me.Range("A:A"))
    
    If Not rng Is Nothing Then
        Application.EnableEvents = False ' Disable events to prevent recursive calls
        For Each cell In rng
            If Not IsEmpty(cell.Offset(0, 1)) Then
                cell.Offset(0, 1).ClearContents
            End If
        Next cell
        Application.EnableEvents = True ' Enable events again
    End If
End Sub

This code will clear the cell in column B (offset by one column to the right) that is in the same row as the changed cell in column A. Make sure you place this code in the worksheet module of the specific sheet where you want this behavior to happen.

[#4]

Thank you so much!!! This worked perfectly 🙂

[#5]

Thank you so much!!!!