Need Help from VBA pro regarding lock/unlock cell

[mohaguru]

Dear Pro,
I want to lock/unlock 2 cells depending on other 2 cells value
Suppose...... My cell is a1, b1, c1, d1

The logic is If a1 and d1 value are greater or smaller than each other then
b1 & c1 remain locked

But If a1 and d1 value are same then b1 & c1 will unlock.

Note plz... I'm totally new in vba that's why I didn't figure out any macro. Please Help me!!

Thanks in Advance...

 

[Peter_SSs]

Welcome to the MrExcel board!

Are the values in A1 and D1 entered manually or do those cells contain formulas or something else?

Are you Protecting the sheet with/without a password?

 

[mohaguru]

Welcome to the MrExcel board!

Are the values in A1 and D1 entered manually or do those cells contain formulas or something else?

Are you Protecting the sheet with/without a password?

Thanks for your reply
Yes! I'm entering value manually in a1 & d1 and other cells are protected without password

 

[Peter_SSs]

Thanks for your reply
Yes! I'm entering value manually in a1 & d1 and other cells are protected without password

OK, try this ..

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled file (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  With ActiveSheet
    If Not Intersect(Target, Union(.Range("A1"), .Range("D1"))) Is Nothing Then
      .Unprotect
        .Range("B1:C1").Locked = .Range("A1").Value <> .Range("D1").Value
      .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
  End With
End Sub

 

[mohaguru]

[TABLE="class: grid, width: 121"]
<tbody>[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Very nice . Its really cheers for me. Could you help me a little more. I've 100+ row to do same. How I will do that in easy way? anyway :thumbsup: for you.

 

[Peter_SSs]

Assuming cells in columns A and D are set to Unlocked and cells in columns B:C are initially set to Locked, try this code.
Note that if the cells in columns A and D of a particular row have their contents deleted (cells are empty) then the cells in B & C of that row will be unlocked (because A & D are equal in that row). Is that OK?

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  With ActiveSheet
    Set Changed = Intersect(Target, Union(.Columns("A"), .Columns("D")))
    If Not Changed Is Nothing Then
      .Unprotect
        For Each c In Changed
          With .Cells(c.Row, "A").Resize(, 4)
            .Cells(2).Resize(, 2).Locked = .Cells(1).Value <> .Cells(4).Value
          End With
        Next c
      .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End If
  End With
End Sub

 

[mohaguru]

Thanks peter. It's working. amazing!!!