Conditional Sort?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a range say A2:D6 I would like to sort that range any time the largest
value in range D2:D6 isn't at the top. I would like to sort highest to
lowest. is it possible to use a conditional format?
 
Eric,

What do you mean exactly by "any time"? Do you want it to be done
automatically without user interaction? In that case, you should use an event
which will compare the value in D2 and MAX(D3:D6), and sort the range
automatically.

If you agree to use the sort menu function, then you can have a conditional
formatting in cell D2, so it would have a yellow background for example if it
is not the largest value. Select "Formula Is" from the first combobox, then
copy "=D2<MAX(D3:D6)" (without quotes).

Stephane.
 
How are the values being entered into the cells? If it is as a result of
formulas in the cells? If so then the range will appear not to sort because
the fomulas will just recalcualte,

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
any time means without user interaction. The values in the range are not from
a formula, they are user entered numbers. I don't undersant the term event.

Just to be clear I would like excel to auto sort when a value in the D2:D6
range is changed.
 
Event Macro fires automatically when certain events happen, eg channge
selection, change entry etc.

Right-click on the sheet tab and select "View Cose" then paste this code
into the module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("D3:D6")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Range("D3:D6").Sort Key1:=Range("D3"), Order1:=xlDescending,
Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Application.EnableEvents = True


End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
An event is something that is happening and when it is happening, Excel can
perform an action. For example, clicking on a cell or a button is an event.
Changing the value of a cell is another event, and this is the one we will
use in your case.

Copy this code to the sheet containing the range D2:D6.

Private Sub Worksheet_Change(ByVal Target As Range)

' If we modify a cell outside the range D2:D6, we exit this routine
without any action
If (Target.Row <= 1 Or Target.Row >= 7) And (Target.Column <= 3 Or
Target.Column >= 5) Then
Exit Sub
End If

On Error GoTo ErrHandler

' Let's suspend the listening to change event, or else we will be in an
endless
' loop as the sort command will change cell values and will trigger this
routine!
Application.EnableEvents = False

' Now, let's sort the range in descending order
Range("D2:D6").Sort Key1:=Range("D1"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

' We allow again the listening to the change event
Application.EnableEvents = True

Exit Sub

ErrHandler:
Application.EnableEvents = True

End Sub

Stephane
 
The code got hit by line wraping try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("D3:D6")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Range("D3:D6").Sort Key1:=Range("D3"), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Application.EnableEvents = True

End Sub



--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Helllo,

I can't seem to get either of these to work right. Stephane I did fix the
line wrapping in your code. Also, Sandy I am have no luck. I did make sure to
save the document as a macro-enabled workbook. The values are percents and I
want the highest % at the top of the range. I did edit the Range in both to
C2:C8 as that is the actual range. I changed the single cell from D3 to C2
as well.
 
Hi Eric,

If I correct the ranges in my code to:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("D2:D8")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Range("A2:D8").Sort Key1:=Range("D3"), Order1:=xlDescending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Application.EnableEvents = True

End Sub

It works for me as you wanted. The only thing that I can think of is: you
should have pasted the code into a sheet module - not a normal module.

If you still can't get the code to run automatically then feel free to send
me a sample sheet by altering my address below as it says in my signature.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top