Named Ranges and Varying Chart Colors by Slice

Hi,



I have two issues that I've been unable to resolve:



1) I have a named range of cells called "District Staff" which = B56:B77

If a user inserts a row anywhere *except for the last cell of the range* the range automatically expands to include the new cell. However, when a user inserts a row by selecting cell B77 and clicking "insert row" then the range does not expand to include cell B78. Is there anyway to fix this?

I've read up on dynamic ranges...and the tricky thing here is that I have several named ranges stacked on top of each other (so it isn't as simple as taking all the cells in column B with data). For example the named range "Central Staff" is B79:B99. I have reprogrammed the insert row command so that the row is automatically inserted above the activecell; however the range still does not expand to include the final row.



2) My application automatically produces pie charts as an output. However, each time I close and reopen the program and enter new data the pie charts become monochrome. If I right click on the chart itself and under "fill" select "vary colors by slice" then the chart looks nice again. Is there anyway to get excel to do this automatically? I have saved and re-saved the program numerous times with these correct settings--but after entering in new data the colors disappear.



Thank you in advance!!

Katie