Conditional formatting

  • Thread starter James Silverton
  • Start date
J

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
 
D

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.
 
J

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
 
R

robert morris

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
 
D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Investigate inherited formatting. 4
Pivot Tables 3
Mysteries on msnews. 2
Randomizing 3
Conditional formatting on a function 3
Checkmark question. 3
how do u make a freytag pyramid 3
Symbol for new line in cell 7

Top