Select Cells For Protect Based On Complete Row Being Blank

Question

I need to protect some data using VBA in a worksheet but leave some cells open so that data can be entered - tab name "Part Order"

My data starts in A5 and goes through to J

I have a couple of issues which I am struggling to resolve - see below

If data is found in column A from cell 5 then lock (A to F) then (H) And Then all cells from (K) to the end of the worksheet this leaves all cells unlocked in G5 down I5 down and J down
If data is not found in column A from cell 5 then lock the complete row

If anyone can help with this it would be most appreciated.

Thanks in advance

show us your attempts please:) – Dmitry Pavliv Feb 12 '14 at 21:08 — Dmitry Pavliv, Feb 12 '14 at 21:08

score 0 · Accepted Answer · answered Feb 12 '14 at 21:50

Try this one:

Sub LockCells()
Dim sLastColName As String
Dim lLastRow As Long
Dim i As Long

With Worksheets("Part Order")
    sLastColName = Mid(.Cells(1, .Columns.Count).Address, 2, _
                       InStr(2, .Cells(1, .Columns.Count).Address, "$") - 2)
    lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    .Cells.Locked = False

    For i = 5 To lLastRow
        If .Cells(i, "A").Value <> vbNullString Then
            .Range("A" & i & ":F" & i & ",H" & i & ",K" & i & ":" & sLastColName & i).Locked = True
        Else
            .Rows(i).Locked = True
        End If
    Next i
End With
End Sub

Select Cells For Protect Based On Complete Row Being Blank

1 Answers1