Help with locking and unlocking cells based on value being inputted

[Manny_H]

I only have a basic understanding of how to do things with excel and I am not sure if it is possible to do what I want to do but thought I would put this out and see if someone could help.  I will do my best to explain hopefully someone will be able to understand.

I have data input in column D and E from rows 13 thru 56.  As I am creating this spreadsheet to be used by others I want to make it as fail proof as possible and would like to have a one cell or other lock if a value is in the other cell. So basically this:

Default value of D13 and E13 is null and unlocked

If D13 =.currancyvalue then

E13 =.locked

If E13 =.currancyvalue then

D13 =.locked

Also want it should a value be removed from either cell then both go back to default. And I need this to continue from rows 13 to 53 so D14 and E14 control each other and so on.  I know to protect worksheet and lock or unlock all cell as needed in worksheet but have not been able to figure out how to make the locking and unlocking of individual cells work.  Does anyone know if this is even possible and can help me with how to do this?

Thank you

[#2]

@Manny_H 

Yes, this is possible. But the solution includes using VBA code, and that means you have to be concerned about security. How much can you trust the other users (and anyone else who will have access to the workbook)? Not only could they bypass your worksheet protection, they will also have the ability to install code that acts maliciously.

Don't take this lightly. If this data is important (e.g., you are following a backup plan), you should be concerned about security. Worksheet protection is weak protection, not strong. And if you are using a password to protect it, the code needs to contain or derive that password (it has to turn protection off - temporarily - to lock or unlock cells), so a smart user will be able to view the password.

If you still want to proceed… You must use a version of Excel that supports VBA: either Windows or Mac (not online or mobile). You will need to save the file as a macro-enabled workbook (most likely with file extension .xlsm). The code will need to respond to user actions, specifically to changes in the worksheet's values, so you will place it in the Worksheet_Change event handler. It appears that you  may know some VBA. But in case not…

 
Make a copy of the workbook to test with. Right-click the relevant tab, and select "View Code". The VBA editor (VBE) will open. It should look approximately like this:

The largest of its child windows is the code window; you will put code here. If the code window does not contain the statement "Option Explicit", enter it now.

Review the following code, and (to make this description simple) paste it into the code window, after the "Option Explicit" statement.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strMessage  As String   'a message to the user
    '  --   In this worksheet:
    Dim rngRangeOfInterest  As Range
    Dim rngIntersection As Range    'changed cells of interest
    Dim rngChangedCell  As Range
    Dim rngAdjacentCell As Range
    Dim vntStoredValue  As Variant
    Dim in4Row      As Long
    
    '----
    On Error GoTo WorkshtChg_ErrHndlr
    
'CheckForChangeOf[...something monetary...]:
    '----   Check for a change in value(s) in the [how would you describe this??] area.
    Set rngIntersection = Intersect(Target, Me.Range("D13:E56"))
    If rngIntersection Is Nothing Then
        '...the change(s) did not involve the column of interest.
        GoTo CheckForNextChangeTypeOfInterest
    End If
    '----   Unprotect the worksheet (temporarily).
    Me.Unprotect
    '----   Conditionally change protections.
    For Each rngChangedCell In rngIntersection
        vntStoredValue = rngChangedCell.Value
        in4Row = rngChangedCell.Row
        Select Case rngChangedCell.Column
            Case 4  'column D
                Set rngAdjacentCell = Me.Range("E" & CStr(in4Row))
            Case 5  'column E
                Set rngAdjacentCell = Me.Range("D" & CStr(in4Row))
        End Select
        '
        If vntStoredValue = "" Then
            '   A value (whether valid or not) was cleared.
            If rngAdjacentCell.Value = "" Then
                rngAdjacentCell.Locked = False
            End If
        ElseIf IsNumeric(vntStoredValue) Then
            '   Assuming it was a currency value [though additional
            '   checks could be added]...
            Select Case rngChangedCell.Column
                Case 4  'column D
                    rngAdjacentCell.Locked = True
                Case 5  'column E
                    rngAdjacentCell.Locked = True
            End Select
        Else
            '   The cell value was changed, but not to a valid
            '   currency value.
        End If
        Set rngAdjacentCell = Nothing
    Next
    Set rngChangedCell = Nothing
    Set rngIntersection = Nothing
    '----   Re-protect the worksheet.
    Me.Protect Contents:=True, AllowFormattingCells:=True _
            , AllowFormattingColumns:=True
    '[You can include a password, but then smart users can see it.]
    
CheckForNextChangeTypeOfInterest:
    'Until there is a need for one...
    Exit Sub

WorkshtChg_ErrHndlr:
    Dim in4ErrorCode    As Long
    Dim strErrorDescr   As String
    '  --   Capture info.
    in4ErrorCode = Err.Number
    strErrorDescr = Err.Description
    '  --   Notify the user.
    strMessage = "Error " & in4ErrorCode & " occurred:" _
            & vbCrLf & strErrorDescr
    Call MsgBox(strMessage, vbCritical)
    '  --
    Exit Sub
    Resume  'inaccessible, but retained for debugging
End Sub

Edit it as appropriate, in particular the Me.Protect statement. Save this copy and test.

Your post references both "rows 13 to 56" and "rows 13 to 53" for the region needing locks; I assumed the former is correct.