Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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