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



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
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)



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


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