Ken Johnson
12/16/2006 10:26:00 AM
Gordon wrote:
> Hi...
>
> I have 3 columns A - C.
>
> In Column A I have values 1 -15 in descending order.
> In Column B I have names of tools.
> In Column C I have values that are subject to change.
>
> What I need is some code that will look at the values column C and then
> shift the tool names in Column B so that it creates a league table. Column A
> can remain unchanged. This all needs to be intuitive and automated so that
> there is no user control.
>
> Thanks
>
> GC
Hi Gordon,
If the tool with the highest Column C value goes to the top of the
league table then you could try...
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C16")) Is Nothing Then
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
Range("B2:C16").Sort Key1:=Range("C2"), _
Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.EnableEvents = True
End If
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub
If the tool with the lowest Column C value goes to the top of the
league table then you could try...
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2:C16")) Is Nothing Then
Application.EnableEvents = False
On Error GoTo ERROR_HANDLER
Range("B2:C16").Sort Key1:=Range("C2"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.EnableEvents = True
End If
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub
Choose the appropriate code, copy it, right click the sheet tab, select
"View Code", paste the code, press Alt + F11 to get back to Excel.
Ken Johnson