On a previous query posted it was suggested I use a table for my datasheet which expands as needed.

However I have come across a couple of problems that I cannot see how to solve.

If the worksheet is protected for editing the table will not expand, how do I get it to expand without having to unprotect the sheet (I am using userinterfaceonly:=True in vb to protect the workbook).

And when the new line is inserted at the end of the table all drop-down lists and colours populate the new line but conditional formats do not.

What am I doing wrong?