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

Max,

You need to past this macro into the workheet code for that sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$9" Then
Me.Range("D10:d12").ClearContents
End If
End Sub
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"max power" wrote:

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

This goes into the worksheet's code module. Easiest way to get there is to
right-click on the sheet tab and choose [View Code] from the list. Then just
cut and paste the following into the module that appears. Close the VB
Editor and give it a test run.

You said "when cell D9 is changed in any way..." This will erase contents
of D10:D12 when the value in D9 is changed - i.e. deleted or edited, not if
someone just changes the formatting of it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("D9")) Is Nothing Then
Exit Sub
End If
Range("D10") = ""
Range("D11") = ""
Range("D12") = ""
End Sub


You can also use
Range("D10:D12")=""
instead of the individual entries for each cell if you prefer.

"max power" wrote:

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