insert row macro with protection and background formatting

capitalyst

New Member
Joined
Jan 7, 2014
Messages
1
Greetings,

I have a spreadsheet (G columns wide) that has "locked" protection on all cells except for the last row of data from column B to E (cells A, F, G are formulas and protected). Users of other similar sheets I have made are used to seeing cells that they cannot edit having an off white backgrounds for user data, yellow bacgrounds for formula calculation cells and cells that they can edit being white (ie, default "none")

I'm trying to write a macro that will find the last row of spreadsheet with data (which at the start of this procedure has the only 4 unlocked cells on entire worksheet in columns B:E), copy this row (to get all the formula and formats), insert a copy at the bottom of the spreadsheet. Next, I want the macro to change the change the 4 white cells on the 2nd to last row ( ie, the old last row) to have offwhite background fill, and have protection changed to locked. Finally, the bottom 4 white cells need the contents cleared of the old data left over from copying so cells look ready for new input.

The majority of my difficulty is with the three steps where the macro is manipulating 4 specific cells. My attempts keep selecting the wrong cells. Grrrr.

Appreciate any help anyone can provide.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,184
Messages
5,857,827
Members
431,900
Latest member
Paradime0346

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top