First of all, select the cells to clear and InsertNameDefine

Give the range a name of TheRange.

Add this event code to the sheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$2" And Target.Value < "" Then 'adjust the $A$2
Me.Range("TheRange").ClearContents
End If
stoppit:
Application.EnableEvents = True
End Sub

Question: How do the cleared cells get re-populated?

Otherwise not much point in doing it automatically each time A2 gets
changed.


Gord Dibben MS Excel MVP

On Tue, 30 Dec 2008 08:45:00 -0800, shaunap
wrote:

I have a worksheet that I would like to have specific cells cleared to
nothing if the value in one specific cell changes. So if I change the one
cell at any time and point then the other specific cells clear out of
anything. Can anyone tell me if this is possible and how I would go about
it. Currently I have a macro set up that clears the cells if you "CTRL + c"
but I would like it to do it automatically.

Thanks for your time.
Shauna