Re: Blank a cell if the contents of another cell deleted

[SATXpotter]

I have a similar situation as mentioned above, on our inventory sheet I have a stock# field (d3), when you enter the stock# it pulls in information and pricing for that unit. I have 3 blank fields, Regular Price K3, Sale Price L3, & Cash Discount N3, these are blank so the salespeople can play with the price to see how it affects our Profit & Margins. What I want is for when the stock# D3 is deleted it will also clear the other 3 fields. When I used the code above it worked perfectly except after clearing the fields it closes Excel completing and then wants to reopen in safe mode. Any idea what would be causing this? I just need it to clear the fields and keep the file open and working so other units can be looked at. Thank you for your help!!
Here is the code I'm using, slightly modified from below...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RangeA As Range
Set RangeA = Range("D3")
On Error Resume Next
If Application.CountBlank(RangeA) = RangeA.Cells.Count Then
Range("K3:L3,N3").ClearContents
Else
Exit Sub
End If
Set RangeA = Nothing
End Sub

This is what I found on an old thread...

An Excel Function cannot perform an action like clearing the contents of another cell. However you can use the Worksheet_Change event with Visual Basic to perform what you need. Simply copy the following code and open your worksheet then press Alt+F11 and paste the code in the Right-Side open pane. Then close the Visual Basic Editor and test it out.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RangeA As Range
Set RangeA = Range("B1:D10")
On Error Resume Next
If Application.CountBlank(RangeA) = RangeA.Cells.Count Then
Range("A1:A10").ClearContents
Else
Exit Sub
End If
Set RangeA = Nothing
End Sub

 

[PureBluff]

I couldn't get my Excel to crash (or the macro to do anything) but it seems over complicated for what you require.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Then
Range("K3:L3,N3").ClearContents
End If
End Sub

 

[RoryA]

If you change cells within a change event, it will trigger the event recursively. You should always disable events temporarily while doing so:

Private Sub Worksheet_Change(ByVal Target As Range)
dim RangeA as range
set RangeA = Range("D3")
If not intersect(Target, RangeA) is nothing then
On Error Resume Next
If Len(RangeA.Value) = 0 Then
application.enableevents = false
Range("K3:L3,N3").ClearContents
application.enableevents = true
End If
end if
End Sub