Please Help!!!! If statement, lock and unlock cells
Hi Bill,
The line:
Dim RngCol As Range
can be deleted as it relates to a discarded variable.
The suggested code locks all populated cells on the specified worksheet. It
unlocks and highlights all blank cells.
It may be, however, that your intention is that highlighting should occur
dynamically in response to the population of previously blank cells. If this
is the case, please post back as the suggested code will require minor
amendment and an event procedure will need to be added
---
Regards,
Norman
"Norman Jones" wrote in message
...
Hi Bill,
Try:
'===================
Public Sub Tester02()
Dim Rng As Range
Dim RngCol As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim i As Long
Const PWORD As String = "YOUR PASSWORD"
Set WB = ActiveWorkbook '<<========== CHANGE
Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
SH.Unprotect PWORD
SH.Cells.Locked = True
Application.ScreenUpdating = False
With SH.UsedRange
For i = 1 To .Columns(.Columns.Count).Column
On Error Resume Next
Set Rng = Columns(i).SpecialCells(xlBlanks)
On Error GoTo 0
If Not Rng Is Nothing Then
Rng.Cells.Locked = False
Rng.Cells.Interior.ColorIndex = 6 '<<======= CHANGE
End If
Next i
End With
SH.Protect PWORD
End Sub
'===================
---
Regards,
Norman
"Bill" wrote in message
...
Hi,
The spreadsheet has 2000 records and 30 columns. There are many blanks
so I just want to lock all the data that fill in cells and unlock for
blank cells for me to input the data. Is there a way to make the cell
turn to bold where I update the blank cell only.
Please help!!! Your help would be much apprecated.
Thanks.
|