# Make the last 5 rows always highlighted

#### randomwalker

##### Board Regular
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.

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

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!]

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

Replies
7
Views
155
Replies
3
Views
135
Replies
4
Views
371
Replies
0
Views
181
Replies
11
Views
416

1,202,901
Messages
6,052,445
Members
444,581
Latest member
naninamu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back