VBA Event to Delete Cell Value Based on Value Entered in Another Cell

[dbmathis]

Hi All,

This used to be easy, but I have been away from Excel VBA for some time.

I want to for example on Sheet2 delete the content of E9 if a value is entered into E10 and then alternatively delete the content of E10 if a value of entered into E9.

Best regards

 

[jbeaucaire]

If it's always JUST those two cells, then this is a very surgical answer. To expand this to other less precise examples would probably require a completely altered approach:

This macro in the SHEET module:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E9:E10")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Address = "$E$9" Then
        Range("$E$10") = ""
    Else
        Range("$E9") = ""
    End If
    Application.EnableEvents = True
End If

End Sub

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E9:E10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Target.Address(False, False)
    Case "E9": Range("E10").ClearContents
    Case "E10": Range("E9").ClearContents
End Select
Application.EnableEvents = True
End Sub

 

[dbmathis]

Thanks to all. Both work.

What is this for?

If Intersect(Target, Range("E9:E10")) Is Nothing Then Exit Sub

 

[jbeaucaire]

The Worksheet_Change is an "event" macro and it is triggered every time any cell on the worksheet is changed. You don't want this code to DO anything unless the cell that was just changed "intersects" with your watched range of cells, E9:E10. If it does, the macro continues, else it just aborts.

 

[dbmathis]

Awesome. Thank you all.

 

[carsoe_peter]

I know this is an old post, but if you would like to expand the VBA code to cover more than just to cells, let's say column A:A and B:B, how will the code then look?

I have tried twerking the code above, but no matter what I do, it just won't work.

Hope someone have an idea that might work.

Thanks in advance.

Peter

 

[jbeaucaire]

Something like this for all of column A:B

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:B")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Column = 1 Then       'if the change was in column A
        Target.Offset(, 1) = ""     'clear column B
    Else
        Target.Offset(, -1) = ""    'else clear column A
    End If
    Application.EnableEvents = True
End If

End Sub

 

[carsoe_peter]

Perfect, it works great - thanks a lot

If you where to re-use the code, but for coloumn C and D for instance, how would the code look like then?

It does not work correctly if you just replace A with C and B with D?

 

[jbeaucaire]

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C:D")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Column = 3 Then       'if the change was in column C
        Target.Offset(, 1) = ""     'clear column D
    Else
        Target.Offset(, -1) = ""    'else clear column C
    End If
    Application.EnableEvents = True
End If

End Sub

I've highlighted in red the tweaks to change to "C:D".

There can only be ONE Worksheet_Change event in a sheet module. So if you want to do more than one pair of columns on the same sheet, say A:B and C:D at the same time, that would require one macro doing all that. For instance:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A:B, C:D")) Is Nothing Then
    Application.EnableEvents = False
    Select Case Target.Column 
        Case 1, 3    'if the change was in column A or C
            Target.Offset(, 1) = ""     'clear column B or D
        Case Else
            Target.Offset(, -1) = ""    'else clear column A or C
    End Select
    Application.EnableEvents = True
End If

End Sub