Clear cell contents when the value on the another cell in another sheet changes

0

HI

I want to clear the contents of certain cells in Sheet2 (B1:B6,C1:C6) when the content of the cell Sheet2 (A1) is changing. i have done using the below code but the difficult part is

Sheet2 (A1) is fetching date value from Sheet1 using a formula (=IF(CarOwners!$I$4>=TODAY(),CarOwners!K4,"YES")). So the formula remains the same but only the value in A1 changes when its corresponding cell K4 in Sheet1 changes. 

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("B1:B6,C1:C6").ClearContents
End If
End Sub

This code works when the formula itself changes but does not work when the value fetched by the formaula changes.

Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Answers

0

Try putting the code in worksheet_calculate.

Private Sub Worksheet_Calculate()
'Code here
End Sub

or in the workbook module in the calculate event:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'Code here
End Sub
Discuss

Discussion

Good idea, I didn't think about it.
MRVMV (rep: 52) Oct 27, '16 at 7:11 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login