Highlight a max number

[mpenkala]

hey gang,
easy one here (I'm assuming).

ColAO contains 18 numbers, from AO3-AO20. The numbers change constantly
while I input data. How can I make the current high number change color? I
know it with conditional formatting, but can't seem to get the formula
correct. Also - if 2 or more numbers are currently the highest, both should
be highlighted.

Thanks,
Matt

 

[akphidelt]

You should be able to get this to work with conditional formatting

Select the area go to conditional formatting

Use this

Cell value is "equal to"

Then type in the next box "=Max(AO3:AO20)"

 

[Gary''s Student]

Conditional format FormulaIs:

=AO3=MAX($AO$3:$AO$20) and copy this down from AO3 thru AO20

pick a nice distinctive background color.

 

[akphidelt]

Use Gary's Students example.

Mine example only works if you select the entire column like Max(AO:AO)

 

[T. Valko]

What version of Excel are you using?

In versions prior to Excel 2007...

Select the range AO3-AO20
Goto the menu Format>Conditional Formatting
Formula Is:

=AO3=MAX(AO$3:AO$20)

Note that if the *entire* range is empty the *entire* range will be
highlighted. To prevent that (if that's a possibility):

=AND(COUNT(AO$3:AO$20),AO3=MAX(AO$3:AO$20))

Click the format button
Select the style(s) desired
OK out

 

[Chip Pearson]

Matt,

You can use Conditional Formatting from the Format menu to do this.
Conditional Formatting is used to change the style of a cell (back color,
fore color, borders, etc) depending on the value of the cell or the result
of a formula.

Select AO3:AO20 and choose Conditional Formatting from the Format menu. In
that dialog, change "Cell Value Is" to "Formula Is" and enter the following
formula in the input box. You'll want to include the '$' characters as
shown:

=$AO3=MAX($AO$3:$AO$20)

Then, click the Format button on the dialog and choose the formatting for
that cell. If the formula returns TRUE (or any numeric value not equal to
0) the chosen formatting will be applied. If the formula returns FALSE or 0,
the formatting is not applied.

See http://www.cpearson.com/excel/cformatting.htm for more info about
Conditional Formatting.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[mpenkala]

Excellent - thanks guys!
Matt

What version of Excel are you using?

In versions prior to Excel 2007...

Select the range AO3-AO20
Goto the menu Format>Conditional Formatting
Formula Is:

=AO3=MAX(AO$3:AO$20)

Note that if the *entire* range is empty the *entire* range will be
highlighted. To prevent that (if that's a possibility):

=AND(COUNT(AO$3:AO$20),AO3=MAX(AO$3:AO$20))

Click the format button
Select the style(s) desired
OK out

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Excellent - thanks guys!
Matt

 

[spottkitty]

I'm trying to do two conditional formats and not getting the results desired
in excel 2003.

The first rule seems to work fine, which is to check the cell value against
a goal cell and turn it bold if the cell is equal or greater than the goal

Formula is =B5>=$B$19

Then I am also trying to highlight the top values in the range, which
sometimes there are more than one with the top value.

Formula is =B5=MAX($B$5:$B$17)

Highlight yellow.

I then copied the formatting over to the other cells in the range. The
numbers over the goal show up bold, but I've got no highlights!

I'd very much appreciate help identifying what I need to do to get this to
work!

Thanks!
Barbara

 

[Jon Peltier]

If you turn cells bold by conditional formatting, that condition is
satisfied, and the next condition (maximum) is not tested, so you don't get
any maximum value highlighted. Reverse the order of the conditions, so that
the maximum is checked first, then the goal.

- Jon