Please Help!!!! If statement, lock and unlock cells

[Bill]

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.

[Norman Jones]

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" <a@a.com> wrote in message news:dvudnSa0kJhTOaneRVn-tQ@rogers.com...
> 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.
>

[Norman Jones]

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" <normanjones@whereforartthou.com> wrote in message
news:uBKlfZKwFHA.460@TK2MSFTNGP15.phx.gbl...
> 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" <a@a.com> wrote in message
> news:dvudnSa0kJhTOaneRVn-tQ@rogers.com...
>> 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.
>>
>
>