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

Question

Hi AllI 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 ThenRange("B3").ClearContentsEnd IfEnd SubSo, 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...

hansvogelaar · Answer

DrJ_Thesising_101&nbsp;
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

nikolinode · Answer

DrJ_Thesising_101&nbsp;UntestedPrivate 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 SubThis 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.

drj_thesising_101 · Answer

Thank you so much!!! This worked perfectly 🙂

drj_thesising_101 · Answer

Thank you so much!!!!

Forum Discussion

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

4 Replies

Resources