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.

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.

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.