Unlock Blank cells

[adamsm]

Hi anyone,

How could I write a standard module code that would unlock all the blank cells in column G on a protected sheet without a password?

My range is from G18:G50.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[VoG]

Try like this

Sub UnLockG()
ActiveSheet.Unprotect Password:="Adam"
Range("G18:G50").SpecialCells(xlCellTypeBlanks).Locked = False
ActiveSheet.Protect Password:="Adam"
End Sub

 

[adamsm]

Thanks for the help. Suppose the adjacent columns C is empty. How could I make the code not to unlock those rows if column G when the present code unlocks the empty cells in column G.

Meaning even if the cells of column G is empty but the columns C does not contain anything, the code would not unlock that row of column G.

Any help on this would be kindly appreciated.

 

[VoG]

Try

Sub UnLockG()
Dim c As Range
ActiveSheet.Unprotect Password:="Adam"
For Each c In Range("G18:G50").SpecialCells(xlCellTypeBlanks)
    If c.Offset(, -4).Value <> "" Then
        c.Locked = False
    Else
        c.Locked = True
    End If
End If
ActiveSheet.Protect Password:="Adam"
End Sub

 

[adamsm]

With your recent code im getting compile error. How could I avoid this?

 

[VoG]

Oops!

Sub UnLockG()
Dim c As Range
ActiveSheet.Unprotect Password:="Adam"
For Each c In Range("G18:G50").SpecialCells(xlCellTypeBlanks)
    If c.Offset(, -4).Value <> "" Then
        c.Locked = False
    Else
        c.Locked = True
    End If
Next c
ActiveSheet.Protect Password:="Adam"
End Sub

 

[adamsm]

Thanks for the help. I do really appreciate it.

 

[Rick Rothstein]

There is a non-looping solution available to this question...

Sub UnLockG()
  Dim Blanks As String
  ActiveSheet.Unprotect Password:="Adam"
  With Range("G18:G50").SpecialCells(xlCellTypeBlanks)
    .Locked = False
    Intersect(.Cells, .Offset(, -4).SpecialCells(xlCellTypeBlanks).EntireRow).Locked = True
  End With
  ActiveSheet.Protect Password:="Adam"
End Sub

 

[adamsm]

Thanks for the help once again. I do really appreciate it.