Problems with an Auto Sort code

[JJRudd]

Practice EPR Tracker.xls
	A	B	C	D	E	F
1	Date Last Saved	Proj C/O Date Color Key:		Overdue	30 Days	90 Days
2	12/16/05 13:51
3	Name	Supv Name	Supv Began	Date began	Last Report	Proj C/O Date
4			2-Jan-05	3-Jul-01	1-Jan-05	1-Jan-06
5			17-Mar-05	9-Apr-87	11-Jan-05	11-Jan-06
6			16-Jan-05	27-May-03	15-Jan-05	15-Jan-06
7			1-Feb-05	3-Jun-03	15-Jan-05	15-Jan-06
8			9-Feb-05	16-Oct-01	19-Jan-05	19-Jan-06
9			20-Jan-05	28-Aug-01	19-Jan-05	19-Jan-06
10			29-Jan-05	28-May-03	28-Jan-05	28-Jan-06
11			3-Feb-05	3-Jun-03	2-Feb-05	2-Feb-06
12			27-Jun-05	13-Sep-05	18-Feb-05	18-Feb-06
13			2-Mar-05	6-May-94	1-Mar-05	1-Mar-06
14			7-Mar-05	22-Aug-90	6-Mar-05	6-Mar-06
15			13-Apr-05	13-May-98	12-Apr-05	12-Apr-06
16			15-Apr-05	22-Sep-99	14-Apr-05	14-Apr-06
17			17-May-05	24-Sep-02	15-Apr-05	15-Apr-06
18			16-Apr-05	24-Sep-02	15-Apr-05	15-Apr-06
19			16-May-05	15-Oct-02	15-Apr-05	15-Apr-06
EPR Tracker

Okay I'm using a code to automatically sort my list using F4. The problem is any time I change any cell, it sorts. Can anyone modify the code I'm using to auto sort only when a cell in column E gets changed? Column F is dependant on E. Here's the code I'm using.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG1 As Range

Set RNG1 = Range("A4:Z100")
RNG1.Select
Selection.Sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

Any help would be greatly appreciated.

 

[Norie]

When do you want the list to be sorted?

A change in which cell should trigger the code?

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim RNG1 As Range 

If Target.Address<>"$F$4" Then Exit Sub
Set RNG1 = Range("A4:Z100") 

Rng1.Sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlGuess, _ 
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal 

End Sub

 

[JJRudd]

I only want it to change when column F changes.

 

[JJRudd]

Anybody able to help me?

 

[JJRudd]

Okay, the above code is not working can anybody else help?

 

[Datsmart]

Try this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(ActiveCell, Range("E4:E100"))
If isect Is Nothing Then
Exit Sub
Else
Set RNG1 = Range("A4:Z100")

RNG1.Sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If
End Sub

It will run when any cell in the Range E4:E100 is changed.

 

[JJRudd]

The above code only auto sorts if a cell in column E becomes blank. I want it to sort if any cell in column E changes at all. Whether it becomes blank or the date changes.
If I can read it right, the section of the code that's causing it to only change when blank is this part:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(ActiveCell, Range("E4:E100"))
If isect Is Nothing Then
Exit Sub
Else
Set RNG1 = Range("A4:Z100")

Does anyone know how to fix this?

 

[HalfAce]

Hello JJRudd,
To limit your worksheet_change to execute on a change in column E only, all you need do is add one line at the top of the code. (ie.)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
Dim RNG1 As Range

Set RNG1 = Range("A4:Z100")
RNG1.Select
Selection.Sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

That help?

 

[JJRudd]

Well, kind of. Where do I make the change in the code you gave me to only change when colum E? Would it look like this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <$E> Then Exit Sub
Dim RNG1 As Range

Set RNG1 = Range("A4:Z100")
RNG1.Select
Selection.Sort Key1:=Range("G4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

 

[HalfAce]

Nope, you would just use it as is.
The column (in this case) is being referred to by its number.
For example:
Column A = 1
Column B = 2
(etc.)
So... the statement
If Target.Column <> 5 Then Exit Sub
translates to:
"If the change did not happen in the 5th column (Column E) Then do nothing.

That help make it any clearer?