desperate and urgent

[max power]

i need a macro that will make a cell value blank when another cell is changed.

i cant use a formula because the cell that needs to be blank has data
validation on it and so takes entry from a list

short example of what i want is:

if cell D9 is changed in any way then cells D10,D11 and D12 will be cleared
( = "")

please help

 

[John Bundy]

Not sure exactly what you want but this does it as best I can understand

Dim dTest As String
Dim myChange As Boolean

Private Sub Worksheet_Activate()
dTest = Range("D9").Value
myChange = False

End Sub

Private Sub Worksheet_Calculate()
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


If myChange = False Then
If Range("D9").Value <> dTest Then
Range("D10") = ""
Range("D11") = ""
Range("D12") = ""
myChange = True
End If
End If

End Sub

 

[Don Guillett]

Please endeavor to use a more descriptive subject line. All requests here
are considered to be desperate and urgent and this can anger some. See if
this helps

right click sheet tab>view code>insert this>SAVE workbook

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$9" Then Exit Sub
Range("d10:d12").ClearContents
'if you want to remove the validation entirely change to .clear instead of
..clearcontents
End Sub