VBA code to clear cell if other cells are changed

[virtuosok]

Hi,
I am trying to convince the following code work for me:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A5:A8")) Is Nothing Then
        Range("A2").ClearContents
    End If
End Sub

...but it doesn't. I have saved the file as .xlsm, added the above piece as a "module" (General/Worksheet_Change) but whenever I update values in cells A5:A8, entry in cell A2 is not automatically erased as expected. Please help?

 

[My Aswer Is This]

Your code should not be in a Module.
Install it like this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

 

[virtuosok]

Thanks, this works.
Is there a way to enhance the code so that whenever cell A5 (or A8) is updated, cell A2 gets auto-erased, and vice versa? (A2 update erases any A5 and A8 entries).

 

[Marc L]

Hi !​

Private Sub Worksheet_Change(ByVal Target As Range)
        Application.EnableEvents = False
    Select Case Target(1).Address
           Case "$A$2":         [A5,A8].ClearContents
           Case "$A$5", "$A$8": [A2].ClearContents
    End Select
        Application.EnableEvents = True
End Sub

 

[virtuosok]

This is awesome, thanks! Case solved!