How to lock cells on the basis of condition on another cell

[darpanshah444]

Hi All,

This is my first post here at Chandoo's. I am attaching my file, where in Cell H2, I have used data validation list for inputting detail (i.e. % or Manual),

I want to arrange cell I2 in such a way that if H2 is % then we can input any numerical value, but in case if cell H2 is manual then the cell I2 should be locked, thus preventing in inputting any value.

Please can you help me with any such conditions ? My initial search on google said it is possible via VBA, I am complete blank about VBA, so if that is the only way, please also suggest how to write it and how to run as well.

Also, if I want to use the same thing till line 1000, what would be the way out ?

Thanks a lot in advance.

 

[Hui]

Darpanshah444

Firstly, Welcome to the Chandoo.org Forums

Why not simplify this by adding a second column for the Manual %


Then change the Conditional Formatting so that it blanks out the other cell


I have changed the CF Format to set the background and text color to the same Burnt Red color so that even if an entry is there it can't be seen

Then use an If Formula in K2 to choose the correct value and do the calculation
Something like: =IF(H2="%",I2,J2)*E2

 

[Nebu]

Hi:

Since I was working on it , here is the VBA solution.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Me.Range("I:I")) Is Nothing Then
    If Me.Range("H" & ActiveCell.Row) = "Manual" Then
        Me.Unprotect
        Me.Range("I" & ActiveCell.Row).Locked = True
    Else
        Me.Unprotect
        Me.Range("I" & ActiveCell.Row).Locked = False
    End If
    Me.Protect
End If

End Sub

Thanks