CODEWITHSUNDEEP
vbaexcel
zozo23
zozo23

Reputation: 11

Clear cell content based on another cell calculation

this is an Excel/VBA question:

I have a cell A1 in sheet2 linked cell A1 in sheet1 (simply A1='sheet1'!A1). A1 in sheet1 is a data validation drop down menu.

I want to clear the content of A2 in sheet2 every time the content of A1 in sheet2 changes/is updated. That is every time the value of A1 in sheet1 is changed using the drop down menu.

I tried using a Worksheet_Change event macro (which I do not fully understand) but it won't work with a cell that updates from a calculation. It also doesn't work if triggered from a cell from another worksheet (I tried linking it to cell A1 on sheet1 in this case). 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    Range("A2").ClearContents
End Sub

Can you think of a simple solution to clear the content of cell A2 in sheet2 when A1 in sheet2 updates?

Upvotes: 1

Views: 5571

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

This works for me...

This code goes in the Sheet code area of Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then _
    Sheets("Sheet2").Range("A2").ClearContents
End Sub

Upvotes: 3

Related Questions