Adding new rows and highlighting the last row of the tab

[legaly]

Hello,

is there a way to accomplish adding multiple rows to the tabs and highlighting the last row? Please do see attachment file.

Tabs have different sizes and are not always at the same positions.

I would welcome any help on how to automatize this process or at least make it easier - adding new rows, highlighting last row.

Thanks in advance,

Tom

[dflak]

What version of Excel are you using? If you have Excel 2007 or later then the problem becomes simpler. I have an example on sheet 1. The conditional format is applied to the table, and the formula is: =ROW() =COUNTA($A:$A).

This will also work with a range instead of table, but tables know how many row they have so the "applies to" range of cells grows only as big as it needs to. With a range if you don't specify enough rows, the conditional format will fail eventually. If you specify too many, you will slow down the spreadsheet unnecessarily by checking rows that don't need to be checked.

[legaly]

Hello, thanks for it. It is working quite nicely, but I can't make it work in my own tables. Can you take a look and tell me what am I doing wrong?

[dflak]

This is a more complicated layout. The table doesn't start on row 1 and there is additional information on rows below it which cause the COUNTA to fail.

You are going to have to invoke a helper cell (It can be anywhere in the workbook, even on a hidden sheet) I chose Cell M10 and it has the formula:
=COUNTA(Tabulka4[Sloupec1])+10 - This gets the number of rows in the table + 10. The plus 10 is because the table starts in row 10.

The Conditional formatting then becomes: =ROW() =$M$10

I tried replacing $M$10 with COUNTA(Tabulka4[Sloupec1])+10, but the conditional formatting can't handle it so it has to remain as a helper cell.