Programatically securing individual rows of data in Excel 2007 spreadsheet
Posted on 2013-01-24
I’ve received an odd request at work and I’m not sure how to even begin going about attacking it. The user I’m trying to help has downloaded what is essentially a flat file then the adventure begins.
The following steps are what has been requested. I’ve asked them to consider different options, but this is what they want and I’ve been unable to move them off of this specific request:
1) Insert two blank rows between each row of data
2) Format the blank rows where the font is italics and the color is red when inputs are added
3) Protect the original rows (do not allow edits), but do allow inputs in the “new” blank rows that are input.
The worksheet does not have to be password protected, only set up to not allow changes to the “original” data.
I’ve found this handy tidbit of code to insert the two blank rows, and then modified it to handle the formatting of the new columns, but that is as far as my skills with excel will allow me to go.
Dim i, nRows, firstRow
Application.ScreenUpdating = False
nRows = 2
firstRow = 1
i = firstRow + 1
While Not IsEmpty(Cells(i, "A"))
Rows(i & ":" & i + nRows - 1).Insert
Rows(i & ":" & i + nRows - 1).Select
Selection.Font.Italic = True
.Color = -16776961
.TintAndShade = 0
i = i + nRows + firstRow
The assistance I need is with Step 3. I tried protecting the sheet first, then running the macro to input the blank rows, but it gave me a 400 error. I guess I’m looking for a way to protect the sheet programmatically, then to go in and “unlock” the rows where there is a blank value in column A (or vice versa, to lock only rows where column A is not blank).
FWIW, I’m working in Excel 2007.
Any help is obviously appreciated on this very unique request.