How do I autofill conditional formatting?

[Guest]

I need to format twenty rows highlighting the max and min of each row, and
the only way I can find to do it is to do each row separately -- if I try to
do it for the entire thing, it either won't do it for rows or will highlight
the same numbers from the previous one. Is there a way to autofill this, or
is there some variable I should put instead of the row number? Please help
-- it takes forever otherwise!

 

[Biff]

Hi!

Try this:

Assume the range of cells is A2:E21

Select the range A2:E31
Goto Format>Conditional Formatting
Condition 1
Formula Is: =AND(ISNUMBER(A2),A2=MIN($A2:$E2))
Click the Format button
Select the style(s) desired
Click OK
Click Add
Condition 2
Formula Is: =AND(ISNUMBER(A2),A2=MAX($A2:$E2))
Click the Format button
Select the style(s) desired
OK your way out.

If all the numbers are the same in any row they will be both the MIN and the
MAX for that particular row. Since condition 1 is defined first and is set
for the MIN, that style will be applied.

Biff

 

[Ragdyer]

The procedure behind copying the conditional format formulas to the entire
range, is exactly the same procedure as copying any other formula down a
column or across a row ... place your absolutes at the right reference
points.

Say your range is A1 to Z20.

Select the *entire* range.

Change "Cell Value Is" to "Formula Is", and enter this:

=A1=MAX($A1:$Z1)
Choose your format.

Then, click "Add" for condition 2,

Change "Cell Value Is" to "Formula Is", and enter this:

=A1=MIN($A1:$Z1)
And choose your format for this condition.

<OK> your way out, and you're done!

 

[Guest]

depending upon how you have you data set up
in the conditional formatting box select the = formula option

using a1 as an example

=a1=min($a1:$Z1)

the $ sumbol stops the col's a to z from Moving when you conditionally paste
the format to the other cells

 

[Biff]

Typo correction:

Assume the range of cells is A2:E21
Select the range A2:E31

Should be:

Select the range A2:E21

Biff

 

[Guest]

Blessings!! That worked really well, and savewd my computer class hours of
time. Thanks!

I still don't get the purpose of the initial "=A1" though. It works fine
without...

 

[Guest]

i just use the =a1 out of habit,, some older version dont work without it

but as you rightly said it will also work without in most cases

Rich