How can I allow a user to change a cell in excel only under particular conditions

Question

In an excel document, column A's whole cells are filled with numerical values, and column B has additional numbers.

I want to develop a VBA script (or other solutions, if there are any) to manage to change the values in column A.

To be clear, cell A1 can only be changed by the user of Excel if cell B1's value is zero. The same holds true for the succeeding rows; hence, row A2 can only be modified if row B2 is equal to zero, and so forth.

I have searched on the internet but I can't find a good solution, in particular, because this must work on a large number of cells.

Answer 1

Copy the next event code in the respective sheet code module, and play with changing:

Option Explicit

Private newVal
Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.cells.count > 1 Then Exit Sub
        If Target.column = 1 Then
            If Target.Offset(, 1).Value <> 0 Then
                Application.EnableEvents = False 'to avoind triggering again the Change event
                  newVal = Target.Value
                  Application.Undo
                Application.EnableEvents = True
            End If
        End If
End Sub