Conditionally formatting highest valued cell?

[brett]

I have 7 rows in the same column of type percentage. I'd like to turn
the text of the highest valued cell red. How exactly is that done with
contional formatting (vs. IF statements for each cell)?

Thanks,
Brett

 

[Biff]

Hi!

Assume the range of cells in question is A1:A7.

Select the range A1:A7
Goto Format>Conditional Formatting
Formula is: =A1=MAX(A$1:A$7)
Click the Format button
Select the Font tab
In the Color drop down select your choice
OK out

Biff

 

[brett]

This isn't doing anything. Also, it seems you are forcing one
particular cell to represent the highest value (A1). Any cell in 1
thru 7 can have the highest value and that particular cell should be
colored. Not some other cell.

Thanks,
Brett

 

[brett]

Here ya go:
http://www.techonthenet.com/excel/questions/cond_format3.php.

Brett

 

[Peo Sjoblom]

You didn't follow the instructions, don't know if you know what a dollar
sign in cell references mean but it means an absolute reference while one
without them is relative, so if you select from A1 to A7, A1 will be the
active cell
and it will be compared to the fixed set of cells $A$1:$A$7 so in A1 the
formula says

A1 is equal to the max value of A1:A7, in A2 it says A2 is equal to the max
value of A1:A7 all down to A7 is equal to the max value in A1:A7 and if you
set a certain format the cell with the max value (any of the cells in A1:A7)
will display that certain format. All this means that yes, the formula
works

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Biff]

Hi!

This isn't doing anything.

Describe the *EXACT* steps that you performed. What formula did you use?

Also, it seems you are forcing one
particular cell to represent the highest value (A1).

No, not at all. When you select the range of cells and apply the CF formula:

=A1=MAX(A$1:A$7)

The reference to A1 is relative and applies only to that cell. The reference
automatically changes with each cell in the selected range.

This does work. I've done it litterally tens of thousands of times!

Biff

 

[Biff]

Yeah, that's another way to do it.

Biff

 

[Max]

Yeah, that's another way to do it.

IMO, the better, more direct way using "Formula Is" option in CF
was already given earlier in your response

 

[Biff]

IMO, the better, more direct way using "Formula Is" option in CF

Yeah, that's also my opinion.

Although I don't know how it works internally, seems to me that if you
select Cell Value Is, then Excel has to "test" to see which of the logical
conditions were selected from the drop down. Sort of like a nested IF:

If logical is "Between" Then
This algorithm
ELSE
If logical is "equal to" Then
This algorithm
ELSE
etc
etc


Using Formula IS you explicitly define that logical condition in the
formula.

Biff

 

[Max]

.. Using Formula IS you explicitly define
that logical condition in the formula.

... which seems a more direct way to get it defined, I guess, with of course,
the flexibility to plonk in advanced criteria beyond the preset options
available under "Cell Value Is". I almost always gravitate towards using
"Formula Is" for CF 99% of the time <g>.

 

[Biff]

I almost always gravitate towards using

"Formula Is" for CF 99% of the time

I'm at 100%

I think "Cell Value Is" is mostly for less experienced users.

Biff