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


Replace @mailinator.com with @tiscali.co.uk


"Eric" wrote in message
...
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?

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.

"Stephane Quenson" wrote:

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.

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


Replace @mailinator.com with @tiscali.co.uk


"Eric" wrote in message
...
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.

"Stephane Quenson" wrote:

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.

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Eric" wrote in message
...
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.

"Stephane Quenson" wrote:

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.

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.

Eric,

I posted here http://www.mediafire.com/?6tat34nf2t9 a sample that works on
my PC, using the code I submitted yesterday. You have to enable macros, of
course.

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


Replace @mailinator.com with @tiscali.co.uk


"Eric" wrote in message
...
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.