jamesblack90
New Member
- Joined
- Jun 9, 2015
- Messages
- 44
Hey guys, am trying to create a simple macro that loops through the headings in row 16, if one of them is blank, then clear and lock all applicable cells in that column.
The locking and unlocking work great! But none of the contents of the cells are being cleared. I've tried .ClearContents and .Value = "" but it just retains the data (if any)
Any ideas?
The locking and unlocking work great! But none of the contents of the cells are being cleared. I've tried .ClearContents and .Value = "" but it just retains the data (if any)
Any ideas?
Code:
Private Sub lock_cells()
Dim i As Integer
Dim j As Integer
i = 19
j = 3
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Do While j < 13
If Cells(16, j).Value = "" Then
Do While i < 22
Cells(i, j).ClearContents
Cells(i, j).Locked = True
i = i + 2
Loop
i = 24
Do While i < 33
Cells(i, j).ClearContents
Cells(i, j).Locked = True
i = i + 2
Loop
i = 40
Do While i < 43
Cells(i, j).ClearContents
Cells(i, j).Locked = True
i = i + 2
Loop
i = 47
Do While i < 60
Cells(i, j).ClearContents
Cells(i, j).Locked = True
i = i + 2
Loop
End If
i = 19
If Not Cells(16, j).Value = "" Then
Do While i < 22
Cells(i, j).Locked = False
i = i + 2
Loop
i = 24
Do While i < 33
Cells(i, j).Locked = False
i = i + 2
Loop
i = 40
Do While i < 43
Cells(i, j).Locked = False
i = i + 2
Loop
i = 47
Do While i < 60
Cells(i, j).Locked = False
i = i + 2
Loop
End If
j = j + 3
Loop
Application.ScreenUpdating = True
ActiveSheet.Protect
End Sub