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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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