Conditional formatting Max to a entire row A1:D1 with E1 being Ma

[SunshineinFt.Myers]

Hi again. Example: Max numbers are in ending column say E1, E2, ect... but I
do have numbers in all other cells that are higher numbers then what is in
cell E1 but I do not want to find their max or min value: A1, B1, A2, B2,
going down but do not want to count them. Only want to highlight the entire
row that has the ending Max and ending low with the colors green and red
being low.

 

[Bob Bridges]

If instead of reading this I sort of cast my eyes
over it in a diffused and unfocused mode, I get the
impression that you want to identify the max and
min values in E, and highlight the entire
corresponding rows in a particular color.

If so, I'd set aside two helping cells and two helping
columns. One helping cell (let's say it's in F1) is
=MAX(E), the other (in G1) is =MIN(E). In the two
helping columns F and G are =E2=F$1 and
=E2=G$1. Thus columns F and G are full of mostly
FALSE and a few TRUE values. Fn is TRUE if that
row has the largest value of E; Gn is TRUE if the
row has the smallest.

Now use conditional formatting to color green the row
where Fn is TRUE, and color red the row where Gn is
true. Write back if you have questions or don't know
what conditional formatting is.

 

[Shane Devenshire]

Hi,

I have to concur with Bob, this way of explaining a problem is most
cornfusing (sp)!

Suppose what you want is to format the row on which the max and min values
in column E are found then

To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format (I choose A120).
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=MAX($E$1:$E$20)=$E1
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK one.
8. Click Add
9. For Condition 2, from the first drop down pick Formula is
10. In the second box enter the formula:
=MIN($E$1:$E$20)=$E1
11. Click the Format button
1. Choose a color and click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, Manage Rules, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=MAX($E$1:$E$20)=$E1
5. Click the Format button and choose a format.
6. Click OK twice
7. New Rule
8. Choose Use a formula to determine which cell to format
9. In the Format values where this formula is true enter the following
formula:
=MIN($E$1:$E$20)=$E1
10. Click the Format button and choose a format.
11. Click OK tree times.