Lock cells based on value of other cells - code simplification query

Ok, longwinded is sometimes the best way to be

The issue that you / I have is that by default, all cells are locked. This means that the when you protect the worksheet only the cells that you have EXPLICITY 'unlocked' will be editable.

So for instance the first time a change is made to the worksheet this code will run:

    If [I11+J11=0] And [I11<>""] And [J11<>""] Then
        ActiveSheet.Unprotect Password:="IPG104"
        [K11:N11].Locked = True
        ActiveSheet.Protect Password:="IPG104"
    Else
        ActiveSheet.Unprotect Password:="IPG104"
        [K11:N11].Locked = False
        ActiveSheet.Protect Password:="IPG104"
    End If

Lets presume that either I11 or J11 is still blank, (as you can only have typed into one of them before causing the 'change' event to trigger).
When the above code runs, it will therefore drop into the 'Else' section unlocking K11:N11 and then protecting the sheet. At that point your are stuck.
You are unable to unlock those cells again by typing into J11, (if that was the one that was still blank), because that cell is now locked, and protected.

I think the first thing you will need to do is have a 'reset' macro that will unlock all of the cells on the worksheet, and then from that point, only lock / unlock the ones you are interested in.

Now that I 'think' I know what you are trying to achieve I shall take a look at it

Glenin,

In the code below I have taken an entirely different approach at the problem, so please let me know if it suits your needs.
Rather than placing this code in the 'Change' event, it is in the 'SelectionChange' event, which is triggered when a new cell is selected.
Rather than getting bogged down in locking & unlocking cells, and protecting / unprotecting the worksheet, the code instead checks when a cell is selected.
When the cell is selected the code will check the other values on that row to see if the cell is valid to have an entry put into it. If it is not it will warn the user, and move the cursor back out of the cell.

There are potentially ways around this protection, but it should stop your casual user from typing in a cell by mistake.
Let me know what you think please, and of course feel free to question anything you are unsure of.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim new_row As Integer
    
    new_row = Target.Row ' Capture which row the cursor has just been placed on.
    
    'Now do your tests to find out if the user is allowed in this cell
    
    ' Receipts that do not include a GST component
    ' Check for the values, (column I is column 9, column J is column 10, new_row is the row that the cursor is now on)
    If Cells(new_row, 9).Value + Cells(new_row, 10).Value = 0 And _
      Cells(new_row, 9).Value <> "" And Cells(new_row, 10).Value <> "" Then
        ' Not allowed entries in columns K,L,M,N (11,12,13,14)
        Select Case Target.Column
            Case 11, 12, 13, 14:
                ' Warn the user
                MsgBox "You cannot use this cell as the reciept does not include GST"
                ' Move the cursor back out of this cell
                Cells(new_row, 9).Activate
        End Select
    End If
    
    ' Receipts that do include a GST component
    ' Check for the values, (column E is 5, I is 9, J is 10, M is 13, new_row is the row that the cursor is now on)
    
    If Cells(new_row, 5).Value <> "" And _
      Cells(new_row, 5).Value = Cells(new_row, 9).Value + Cells(new_row, 10).Value + Cells(new_row, 13).Value Then
        ' Not allowed entries in columns O,P,Q,R (15,16,17,18)
        Select Case Target.Column
            Case 15, 16, 17, 18:
                ' Warn the user
                MsgBox "You cannot use this cell as there is not a GST exclusive amount"
                ' Move the cursor back out of this cell
                Cells(new_row, 9).Activate
        End Select
    End If
End Sub

Hi Phil_V,

Early am here and just did a quick test using your excellant suggestion.

Seems to work exactly as required, but as I'm tied up with meetings till about midday I won't have a chance to play with it further till I get back to the office. I'll contact you again later today.

Many thanks for your time effort and help.

Cheers.

Thanks for the feedback, will wait to hear more from you.
Note that you are in Aus and I am UK tho, so nearly midnight here, so will be a good few hours before I get back on here

Hi Phil_V,

Apologies for the belated further response - I've been to Borneo since my last post and under pressure since getting back a few days ago.

Your code is just perfect - works like a charm!

Again my thanks.

All the best.