Make the last 5 rows always highlighted

[randomwalker]

Hi,

I have a range, say from a1 to e100, filled with numerical values. I highlighted last 5 rows which is row96 to row100. I then ordered the range (descend or ascend) according to some criteria. After I ordered the range, the 5 rows highlighted at the beginning moved because of the ordering. Is there a way to make the last 5 rows always highlighted no matter how I order the range?

Thanks

2009-3-16

 

[Richard Schollar]

Hi

1. Go Insert>Name>Define and define a name called "lstrw" as:

=MATCH(9.9E+307,$A:$A)

2. Select A1:E100 and go Format>Conditional Formatting
3. Chgange to FormulaIs and use:

=AND(lstrw-ROW()<=5,ROW()<=lstrw)
4. Hit the Format button and format as required.

 

[Peter_SSs]

If there is always 100 rows, try this:

1. Select A96:E100

2. Format|Conditional Formatting...|Condition 1|Formula is: 1|Format...|Patterns tab|choose colour|OK|OK

 

[randomwalker]

If there is always 100 rows, try this:

1. Select A96:E100

2. Format|Conditional Formatting...|Condition 1|Formula is: 1|Format...|Patterns tab|choose colour|OK|OK

[Thanks Peter. It works perfectly!]

 

[randomwalker]

Hi

1. Go Insert>Name>Define and define a name called "lstrw" as:

=MATCH(9.9E+307,$A:$A)

2. Select A1:E100 and go Format>Conditional Formatting
3. Chgange to FormulaIs and use:

=AND(lstrw-ROW()<=5,ROW()<=lstrw)
4. Hit the Format button and format as required.

[I tried your approach too, but it doesn't work.]