Hi all,

Suppose my range A1 to A7 contains following numbers:
before sort After Sort
---------- ----------
7 1
5 2
2 4
8 5
6 6
4 7
1 8
-- If I change 8 as 3 then it should be automatically sorted,
and expected list should be 1 to 7...is it possible?
please.


--
jamex
------------------------------------------------------------------------
jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243
View this thread: http://www.excelforum.com/showthread...hreadid=547434

Hi
Assuming your numbers are in A1 to A7 on Sheet 1. Then put this code in
the code module behind sheet 1:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ChangeCell as Range
Set ChangeCell = Intersect(Target, Range("A1:A7"))
If Not ChangeCell Is Nothing Then
Range("A1:A7").Sort Key1:=Range("A1"), Order1:=xlAscending
End If
End Sub

To get to that code module, click Alt+F11 to open the VB Editor then
double click the reference to Sheet1. Paste in the above code.

regards
Paul


jamex wrote:
Hi all,

Suppose my range A1 to A7 contains following numbers:
before sort After Sort
---------- ----------
7 1
5 2
2 4
8 5
6 6
4 7
1 8
-- If I change 8 as 3 then it should be automatically sorted,
and expected list should be 1 to 7...is it possible?
please.


--
jamex
------------------------------------------------------------------------
jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243
View this thread: http://www.excelforum.com/showthread...hreadid=547434

Thanks to all , really excellent Mike and all friends

Cheer,
Jamex


--
jamex
------------------------------------------------------------------------
jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243
View this thread: http://www.excelforum.com/showthread...hreadid=547434

I hope this helps, add this in the sheet that you need your data sorted.
Just in case you are not familiar with it, go to _T_ools, _M_acro,
_V_isual Basic Editor (Or you can just press Alt-F11). Under VBA
Project, select the sheet your data is in and enter the following:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:A7")) Is Nothing Then

Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

End If

End Sub


--
Bearacade
------------------------------------------------------------------------
Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016
View this thread: http://www.excelforum.com/showthread...hreadid=547434

This is probably not the best way, but one way...

Right click on worksheet tab
Select "View Code"
Change (General) to Worksheet
Now enter the following Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub
--
Mike Q.


"jamex" wrote:


Hi all,

Suppose my range A1 to A7 contains following numbers:
before sort After Sort
---------- ----------
7 1
5 2
2 4
8 5
6 6
4 7
1 8
-- If I change 8 as 3 then it should be automatically sorted,
and expected list should be 1 to 7...is it possible?
please.


--
jamex
------------------------------------------------------------------------
jamex's Profile: http://www.excelforum.com/member.php...o&userid=32243
View this thread: http://www.excelforum.com/showthread...hreadid=547434