7th & 8th back ground color in a cell

[hsandeep]

Numerical values are there in cells A24:N24 with EXCEPTION being there MAY BE some cells within the range A24:N24 which will contain either blank, alphabet(s) or alpha-numericals. (here D24 & H24).
I have applied 6 CFs in each cell. Each CF is meant to generate 1 back ground color so that out of 6 colors; only 1 is generated at a time in every cell depending on the cell's value (having numerical values).

My requirement is: I want to ADD 7th back ground color (in the cell containing numerical value) if the value is HIGHEST in the range & 8th back ground color (in the cell containing numerical value) if the value is LOWEST in the range.

Note: When either of 7th or 8th color would be populated, it should stop processing other 6 CF conditions.

Such more rows (A24:N24) are there running down in the range A24:N200.
I am using Excel 2007.

How to ADD 7th & 8th back ground color (in the cell containing numerical value)?
Thanks for help.


M4

A

B

C

D

E

F

G

H

I

J

K

L

M

N

JSPL

Bu

Highest

Lowest

<tbody>
[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="bgcolor: #fd7c1b, align: right"]220.58[/TD]
[TD="bgcolor: #fe72ff, align: right"]218.42[/TD]
[TD="bgcolor: #ff00ff, align: right"]218.64[/TD]

[TD="bgcolor: #ff00ff, align: right"]218.98[/TD]
[TD="bgcolor: #ff00ff, align: right"]217.47[/TD]
[TD="bgcolor: #ff00ff, align: right"]217.14[/TD]

[TD="bgcolor: #ff00ff, align: right"]218.18[/TD]
[TD="bgcolor: #ff00ff, align: right"]216.98[/TD]
[TD="bgcolor: #ff00ff, align: right"]217.65[/TD]
[TD="bgcolor: #ff00ff, align: right"]217.39[/TD]
[TD="bgcolor: #ff00ff, align: right"]217.53[/TD]
[TD="bgcolor: #ff00ff, align: right"]210.61[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[hsandeep]

In the cell containing numerical value: the CFs are
=AND($DO$2=0,$BB24=1) CF1 i.e. color1
=AND($DO$2=0,$BB24=2) CF1 i.e. color2
=AND($DO$2=0,$BB24=3) CF1 i.e. color3
=AND($DO$2=0,$BB24=4) CF1 i.e. color4
=AND($DO$2=0,$BB24=5) CF1 i.e. color5
=AND($DO$2=0,$BB24=6) CF1 i.e. color6

Moreover if only 1 cell in the range has numerical value, than 7th & 8th back ground color in the cell is NOT TO BE ADDED.

 

[taurean]

What problem are you facing with this?

 

[hsandeep]

I want to ADD 7th & 8th back ground color (color7 & color8) in the cell containing numerical value which meets the above criteria.

Need formula in the CF to do that.

 

[taurean]

1. Select complete range A24:N24 and then goto Conditional formatting and select new rule.
2. Choose "Format only top or bottom ranked values"
3. First choose "Top" and in the adjacent filed change the default value 10 to 1 and the define the formatting.and press "OK"
4. Repeat steps 1 to 3 for "Bottom".
5. You can prioritize the sequence of CF using UP and DOWN arrows.

 

[hsandeep]

I'll try these & revert back. I can't say whether it will take care of "there MAY BE some cells within the range A24:N24 which will contain either blank, alphabet(s) or alpha-numericals. (here D24 & H24)" or not?

 

[taurean]

Blanks and alpha cells don't affect CF from working.

For formula, select the range A24:N24 and write a formula like below for max:
=A24=MAX($A24:$N24)
and for MIN
=A24=MIN($A24:$N24)