How can you lock cell conditionally

S

SAM SEBAIHI

How can you conditionally (using the condition format) lock the cell?

Thank you in advance!
 
C

Chip Pearson

Not with conditional formatting. What cell do you want to protect based on
what condtion? This could be done with VBA if you provide more detail.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)






How can you conditionally (using the condition format) lock the cell?

Thank you in advance!
 
K

Ken Johnson

Hi Sam,

This seems to work...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(1)) Is Nothing Then
Me.Unprotect "sam"
Dim rngCell As Range
For Each rngCell In Intersect(Target, Columns(1))
If rngCell.Value = "" Then
rngCell.Offset(0, 1).Locked = True
Else: rngCell.Offset(0, 1).Locked = False
End If
Next rngCell
Me.Protect "sam"
End If
End Sub


I've used "sam" as the worksheet protection password, so change that to
match the password you are using, or delete it if you are not using
one.

To get the code in place...

1. Copy it
2. Right click the worksheet's tab then select "View Code" from the
popup menu
3. Paste the code into the code module that appears
4. Get out of the Visual Basic Editor by either pressing Alt + F11 or
going File|Close and Return to Microsoft Excel.

The Security setting of the workbook will need to be "Medium" and
"Enable Macros" on the "Security Warning" dialog will have to be
selected when the workbook opens.

If this is not already the case then go Tools|Macro|Security... click
on "Medium"|OK|Close|Open|click "Enable Macros" on the "Security
Warning" dialog|OK.

Ken Johnson
 
S

SAM SEBAIHI

Thank you chip,

I need for example to lock cell B1 if cell A1 is empty, same with B2 to be
locked if A2 is empty. If A3 is not empty, then I don't want to have cell B3
to be locked...etc..
thank you in advance.

==================================================
 
Top