clear cells on change of another cell via VB

[asylum]

HI,

On Sheet one i have cell a1, which is a picklist of dates. When this changes value I woudl like to clear the contents of teh cells in range a1:d90 in sheet2, what would be a good way of doing this via VB?

Thanks

Andy

 

[VoG]

Try this: right click Sheet1's tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then Sheets("Sheet2").Range("A1:D90").ClearContents
End Sub

 

[asylum]

Thats great thanks, how would I modifiy it if I wante dthe same action if any cell in range a1:d40 on sheet1 happend?

CHeers

Andy

 

[VoG]

Try

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

 

[asylum]

HI, that did the trick very well, thanks, I stacked up a few conditions and it acted just as i wnated, many thanks