Restrict Cell Input Based On Another Cell's Value

[dasbear3]

Greetings all,

Is it possible to set conditions on a cell that would make it unusable (blocked out from editing, filled in black?)

If A2 is $500,000 or more: B2 populates with "YES"
If A2 is under $500,000: B2 is disabled from edits, filled in black

Wasn't sure whether this was possible through Conditional Formatting or VBA.

Thank you for your time!

 

[RonEmm]

To lock cells you would need to set your worksheet to Protected. Then, in your example, you would need to unlock all cells in the columns A and B (via cell properties). This could have a serious impact on the rest of your sheet. I therefore chose to delete any input in cel B2 if A2 is less than 500000. Hope this will help you. You should place this code behind your input sheet as a private sub.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        If IsEmpty(Target) Then
            With Target.Offset(, 1)
                .Interior.Pattern = xlNone
                .ClearContents
            End With
        ElseIf Target.Value >= 500000 Then
            With Target.Offset(, 1)
                .Value = "YES"
                .Interior.Pattern = xlNone
            End With
        Else:
            With Target.Offset(, 1)
                .Interior.ThemeColor = xlThemeColorLight1
                .ClearContents
            End With
        End If
    End If
End Sub