Conditional formatting based on a row number

**dlwoodsaust** · 03-15-2012, 12:45 AM

I am developing a table to manage client sales and want to be able highlight the top 20% of clients in the table.

I just need some help on the conditional formatting.

I already have a cell which counts the numbers of rows that contain client data based on client name in column A. Currently this is 24
I also have a cell which calculates 20% of this number of columns = 4.8 round up to 5

I was told by a colleague to use the following formula in conditional formatting:

=$A2=Row()<=$A$53+2

Where A2 is the first cell in the Client Name column and A53 is the cells containing the calculation of 20% of total rows containing client data (e.g. 5)

Based on this formula cells in column A corresponding to rows numbered less than or equal to 7 should be filled in red.

I received no error message re my formula but the conditional formatting does not seem to work. I assume the formula is wrong.

Could someone be kind enough to let me know how I should do this.

**DGagnon** · 03-15-2012, 01:04 AM

could you please share a sample of your workbook?

**Prawny** · 03-15-2012, 07:08 AM

I think this is what you are looking for...

Conditional Formatting range applies to $A$2:$A$1000 (change if you may have more than 998 records/rows)

Formula for Conditional Formatting:

=ROW(INDEX(A:A,ROW()))<$D$9+2

Example: Row Number Based CF.xlsx

**DGagnon** · 03-15-2012, 12:33 PM

your sample file appears to be working for me, if i change the value in Cell D9 the number of highlighted rows also changes.

if you are getting errors, try this

=ROW($A2)<$D$9+2