Conditional formatting

[James Silverton]

Hello, All!

I did not think this would be a problem but I am having
difficulty setting it up. If the answer is RTFM, please tell me
where

What I want to do is to have the contents of cell colored red if
it is greater than the maximum of all previous values in the
column.

TIA

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Dave Peterson]

With the data in A1:Axxx, I selected A2:Axxx (omitted A1).

Then format|conditional formatting
formula is:
=AND(A2<>"",A2=MAX($A$1:A2))
And gave it a red format

I ended up with quite a few red cells in my test data. But those red cells were
the highest so far.

 

[James Silverton]

Dave wrote on Sat, 07 Jul 2007 09:27:26 -0500:

DP> Then format|conditional formatting
DP> formula is:
DP> =AND(A2<>"",A2=MAX($A$1:A2))
DP> And gave it a red format

DP> I ended up with quite a few red cells in my test data. But
DP> those red cells were the highest so far.

DP> James Silverton wrote:
??>>
??>> Hello, All!
??>>
??>> I did not think this would be a problem but I am having
??>> difficulty setting it up. If the answer is RTFM, please
??>> tell me where
??>>
??>> What I want to do is to have the contents of cell colored
??>> red if it is greater than the maximum of all previous
??>> values in the column.

Thanks Dave,

I started with =AND($U36<>"",$U36>MAX($U$34:$U35)), the data
beginning in U34, and it works well, happily extending itself to
highlight the current maximum as the table in added to. It is
not important that there are earlier red numbers.

I guess I had forgotten that the function had to be given two
values for true and false. I was using
=IF( $U36>MAX($U$34:$U35)).
It seemed to me that logically it should work because the answer
can be true or false but I was wrong.

Come to think of it, =IF( $U36>MAX($U$34:$U35), 1,0) seems to
work too.

Thanks again,


James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Guest]

You might also try;

in A1 Conditional Formating >New Rule
=A1=MAX($A$1:$A$Axxx)
Applies to: =$A$1:$A$xxx

Format Fill Color

This will return the single maximum value unless there is more than one of
the highest value, in which case it will return all

Bob

 

[Dave Peterson]

If excel can interpret something as true/false when it's expecting a true/false
response, it'll help out.

And in your case (1 or 0), 0 will be treated as false and non-zero will be
treated as true.

The reason I wanted two conditions (a2<>"" and a2=max($a$1:a2)) was so that
empty cells wouldn't be shaded red.