On 18 Jan., 22:44, anduare2
wrote:
I have viewed *threadid=499263 and with that added this modified code to my
main xl worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
'This tells the sub not to work if the column is number 6 or more or 4 or
less
* * If ActiveCell.Column = 6 Then
* * * * End If
* * If ActiveCell.Column <= 4 Then
* * * * End If
* * Exit Sub
'This tells the sub to only work if Column 5 is modified
* * If ActiveCell.Column = 5 Then

'This is your column range and the E167 below is telling the sort where to
start
' I think this range selection area may be my main problem * * * * * *
* * * * * * Range(Selection, Selection.End(xlDown)).Select
* * * * * *Sheets("KPC Sales Report").Select
* * * * * * Range("E167").Select
* * * * * * Selection.Sort Key1:=Range("E167"),
* * * * * * Order1:=xlAscending,
* * * * * * Header:=xlGuess,
* * * * * * OrderCustom:=1,
* * * * * *MatchCase:=False,
* * * * * *Orientation:=xlTopToBottom,
* * * * * *DataOption1:=xlSortNormal

* * End If
End Sub

This does not do anything when I type data into cell area E167 (column 5) or
lower, is it because I am telling it to sort if a column is edited but then
trying to tell it to sort only a portion of the column rather than the whole
column?

I do not write vb code so this is just what I have been able to piece
together. *I am attempting to sort *cells e167 thru f(x) where the (x) will
be an end down selection, as this range will grow as new data is entered. *I
want it to auto sort when a new entry is made in that cell area so the list
box that uses this range will always be in alpha order. *If there is an
easier way to do this please let me know, I may be going in the wrong
direction completely.

Thanks *in advance for any light you can shine on this for me.
(the more explanation you can include the better, dont feel like you will be
talking down to me I am just trying to learn as I go)

Thanks again

Hi

This will sort cells E167 and down cell E167 or lower is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Target.Row = 167 Then
Range("E167", Range("E167").End(xlDown)).Select
Selection.Sort Key1:=Range("E167"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

Regards,

Per