Lock a row with an entry in Col B on saving or closing workbook

Hi,

My first posting so apologies in advance if I offend the rules.

I have a spreadsheet for recording and summarising particular transactions. Column B records the date and Col C details the transaction type (from a data valdation list). Using conditional formatting the cell (in the same row) to whch the transaction value is to be entered is highlighted.

Other cells in the row contain formulas and are locked and the sheet is password protected.

On saving or closing the workbook I want to lock all the cells (used or unused) in any row in which there is a date in column B. How can I do this?

I'm new to vba so detail of what code goes where will be appreciated.

Thanks.

Press Alt+F11 to open the Visual Basic Editor, double-click ThisWorkbook in the Project window, and paste this code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim rB      As Range
    Dim cell    As Range
    
    With Worksheets("Sheet1") ' change as appropriate
        .Protect Password:="secret", UserInterfaceOnly:=True
        Set rB = Intersect(.Columns("B"), .UsedRange)
        If rB Is Nothing Then Exit Sub
        For Each cell In rB
            cell.EntireRow.Locked = IsNumeric(cell.Value) And Not IsEmpty(cell.Value)
        Next cell
    End With
End Sub

Change the sheet name and password appropriately.

Save as type xlsm.

Thanks for the speedy response.

After saving and on trying to close the worrkbook I'm getting the following message.

Run-time error '1004'
Unable to set the Locked property of the Range class.

When I hit the debug button on the message panel the 4th last line of the code is highlighted

cell.EntireRow.Locked = IsNumeric(cell.Value) And Not IsEmpty(cell.Value)

When I hover the mouse cursor over the IsNumeric(cell.Value) section of that highlighed code line a floating message box box identifies the cell value as the first of the header rows in column B (all the header rows are text).

Maybe the IsNumeric bit is the issue? If so, is there some sort of "is anything" statement that can be used instead - locking the (text) header rows is not a problem.

Post a workbook.