Automatically copy formatting and formulas to the next row

[TraciAnnNeedsHelp]

In almost all of my spreadsheets I have allot of formulas and special
formatting for the records. Each time I add a new record to the next
available row in the spreadsheet I have to "Fill Formatting Only" from the
previous row so that everything looks consistent.

I would like to eliminate this step by automating it however possible.
Especially for the spreadsheets that other users update. They always forget
to copy the formulas and end up entering the values rather than allowing the
spreadsheet to calculate.

It would especially be nice if the solution would allow me to lock all the
"non entry" cells so users can't even select them.

Thank you in advance!!!
TraciAnn

 

[Gary''s Student]

For your first issue:

Tools > Options > Edit > check the extend formulas and formats checkbox

for your second issue:

protect the cells containing formulas.

 

[Dave Peterson]

You could lock the cells you want and unlock the input cells. Then protect the
worksheet.

Then provide a macro that would:
unprotect the worksheet
do the insert/copy
reprotect the worksheet

David McRitchie shares some code:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[chris]

Well, if you want the cells to adjust formatting automatically on the
basis of value entered then you will have to write a VBA code ;
similarly to lock the cells you can use the protection from
tool>protection>protect sheet

Chris