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

 

[carsoe_peter]

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".[/code]

Thank you so much for your help. Is it possible to change the code so that it does not affect the 1. row? As I will need to place headlines in cell C1 and D1...?

 

[jbeaucaire]

Thank you so much for your help. Is it possible to change the code so that it does not affect the 1. row? As I will need to place headlines in cell C1 and D1...?

We could, but it seems like an unnecessary code bloat. Do your layout including adding the headers and such, THEN add the code to the sheet module. Yes?

 

[carsoe_peter]

Okay, it works now - don't know what went wrong yesterday

Thank you so much for your help, much appreciated.

 

[MannStewart]

Hi everyone,

I have range in column A from A2:A200 with a corresponding range of data in column B2:B200. I only need my workbook to selectively clear the contents of cells in A2:A200 that corresponds to a specific value returned by my function in B2:B200, if that value in B:B happens to be 0, everytime before I close the workbook. Can anyone show me the way to the vba code to do above?


Many thanks and Happy hols,
Stewart.