VBA to Auto sort column when new data is added to a cell

[dan4]

Hello, I have a worksheet with with 10 columns of data used references in a data validation list. I want to automatically sort the column when new data is added. For example:

Column A = Inquiry Status (Closed, Open, Pending)
Column B = Source (Internal, External)
Column C = Country (Australia, Belgium, China, etc.
etc. etc.

If I add a new Country to Column C, I would like just Column C auto sorted with the header in C1 remaining in place.
If I add a new Source to Column B, I would like just Column B auto sorted with the header in B1 remaining in place.

Etc. Etc. Any help would be appreciated.

 

[JEC]

Try to put this behind the right sheetmodule, but the rest is sorting too, based on the specific column you are changing

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Or Target.Column = 3 And Target.Count = 1 Then Cells(1, 1).CurrentRegion.Sort Cells(2, Target.Column), 1, , , , , , 1
End Sub

 

[JEC]

If you don't want that you can try

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 2 Or Target.Column = 3 And Target.Count = 1 Then
   Cells(1, 1).CurrentRegion.Columns(Target.Column).Sort Cells(2, Target.Column), 1, , , , , , 1
 End If
End Sub

 

[dan4]

Hello and thank you. It worked for columns 2 and 3. Can you please advise the code change to include Columns 1-10?

 

[JEC]

Changing the first line of code to check if the target.column is 10 or lower

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column <= 10 And Target.Count = 1 Then
   Cells(1, 1).CurrentRegion.Columns(Target.Column).Sort Cells(2, Target.Column), 1, , , , , , 1
 End If
End Sub