Find Smallest and Largest.

[Guest]

I have a column of random numbers and need to find the smallest, second
smallest, third smallest, third largest, second largest, largest and
highlight these numbers with different font colors.

 

[Don Guillett]

You might try looking in the help index for SMALL

 

[ilia]

Use conditional formatting. Unfortunately if you have Excel 2003 or
earlier, you can only set 3 rules, meaning 3 different colors.

Select the entire range, activate first cell. My example is a list of
random numbers in cells A1:A42. I select the entire range, and
activate A1.

Then, in conditional formatting dialog box use "Formula is" and type
in this:

=A1=SMALL($A$1:$A$42,1)

This will return TRUE for the smallest number in range. Select
Format, and choose a different font color.

For second smallest number, use this "Formula is" rule:

=A1=SMALL($A$1:$A$42,2)

The opposite of this is the LARGE() function. Largest number in range
will be found like this:

=A1=LARGE($A$1:$A$42,1)

Once you've made up the rules, recalc a few times to ensure you are
getting the correct behavior.

 

[Guest]

Thank you for your response. I am using Excel 2000 and am limited to 3
rules. In that case, would it be possible to combine the smallest, second
smallest and third smallest into one rule and thus use the same font color to
identify all three?

 

[Don Guillett]

Try this idea for condition 1. Add small,2 in the middle.
=OR(O3=SMALL($O$2:$O$8,1),O2=SMALL($O2:$O8,3))

 

[Don Guillett]

Or 6 different colors if 6 different values. ie: three smallest the same =
color 34

Sub colorsmallest()
Set Rng = Range("p218")
For Each c In Rng
Select Case c.Value
Case Application.Small(Rng, 1): mc = 34
Case Application.Small(Rng, 2): mc = 35
Case Application.Small(Rng, 3): mc = 36
Case Application.Large(Rng, 1): mc = 37
Case Application.Large(Rng, 2): mc = 38
Case Application.Large(Rng, 3): mc = 39
Case Else
mc = 0
End Select
If IsNumeric(c) Then c.Interior.ColorIndex = mc
Next
End Sub