Add Event Code to Clear the Customer Cell

[whytewolves]

I have a worksheet that has 3 dependent cells following the first. I need to clear all cells when first data validation box is changed
This code i found only clears the cell following the target cell, and not the other two cells following.

Bills	Creditor	Type	Company
Court
Credit	Capitol One
Entertainment	Shopping	Online
Past	PastUtilities	Jon	Electric

<tbody>
</tbody>

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub


Id also like to be able to add say "ATM" to Credit without having to insert credit into column A again each time.
I have Data Validation and my lists created, just dont want to have to pick same Bill for a new creditor each time.

 

[JLGWhiz]

So, I would name my sheets Jan - Dec and this macro will work only on those sheets in the workbook?

No, not what I meant. I mean that if you only have sheets in the workbook that you want the code to apply to then you can use the Workbook_SheetChange event in the ThisWorkbook code module to make the code applty to whichever active sheet you make the change to. If there are sheets you do not want the code to apply to, they would have to be execepted in the code so that they the code would not screw up the data on those sheets. Otherwise, you could go ahead and put the first code into the sheets you want it to apply to.

In simple terms, any sheet that is not layed out the same as the sheet you originally intended for the code to apply to would most likely be adversly affected if the code ran for those sheets.

 

[whytewolves]

I understand
Ill just use the code in each sheet that I need this to happen in.

 

[JLGWhiz]

I understand
Ill just use the code in each sheet that I need this to happen in.

That's the safe way,
regards, JLG