Disabling cells in excel based on condition

**rajendra_1510** · 01-27-2009, 05:21 AM

Hi..

I have a dropdown in column A(with values Yes and No)

If i choose Yes(Say cell A2) from the dropdown,all the cells of the corresponding row(B2,C2,D2,E2,F2....) should be enabled..This is the default way excel works.

My requirement is ..

If i choose No(Say cell A3) from the dropdown,certain cells of the corresponding row have to be disabled(Say C3,E3 and J3)

Please help me over this.

Thanks in advance..

**royUK** · 01-27-2009, 05:26 AM

What kind of drop down are you referring to?

**rajendra_1510** · 01-27-2009, 05:42 AM

I created a dropdown using Data-->Validation-->List where List is the list of values(Yes and No) stored in some other cells.

**rajendra_1510** · 02-08-2009, 07:02 AM

Please help me over this.

**royUK** · 02-08-2009, 08:03 AM

You could have found very similar code if you had searched the Forum!Try this

Option Explicit
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column > 1 Or Target.Count > 1 Then Exit Sub
ActiveSheet.Unprotect
If Target.Value = "Yes" Then
Range(Cells(Target.Row, 2), Cells(Target.Row, 5)).Cells.Locked = True
Else: Range(Cells(Target.Row, 2), Cells(Target.Row, 5)).Cells.Locked = True
End If
ActiveSheet.Protect
End Sub

To add it to your worksheet, do the following:

Copy the code that you want to use
Select the worksheet in which you want the code to run
Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.
Where the cursor is flashing, choose Edit | Paste