lock cell1 if cell1 = cell2

[Ky1eb88]

To be more exact I have a formula set to auto populate cell A2 with data that is entered in cell B1, and cell A3 with cell B2 ect...


I want the cell in column A to lock any time its value is equal to the value in column b on the previous row.


I'm new to VBA and cant work my head around on how to get this to going. I was able to get it to work with single cells, but even replicate that anymore.


Thanks,
Kyle

 

[Ky1eb88]

Try this AND PLEASE, PLEASE, PLEASE PUT YOUR PASSWORD IN THE CODE WHERE INDICATED BY THE BOLD RED COMMENT.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lR As Long, c As Range
lR = Range("E" & Rows.Count).End(xlUp).Row
If Not Intersect(Target, Range("H1:H" & lR)) Is Nothing Then
    Me.Protect Password:="", userinterfaceonly:=True  'Add your password between the " marks
    For Each c In Range("E2:E" & lR)
        If c.Value = c.Offset(-1, 3).Value Then
            c.Locked = True
        Else
            c.Locked = False
        End If
    Next c
End If
End Sub

Currently the sheet has no password its blank

 

[JoeMo]

Currently the sheet has no password its blank

Then install the code I last posted as is - after you have unlocked columns E and H and protected the sheet. Then make a change in any cell in H to get started.

 

[Ky1eb88]

I get the same error, also tried using password.

 

[JoeMo]

I get the same error, also tried using password.

Works fine for me so I can't reproduce or diagnose your error. Are any of the cells in column E and/or H merged?

 

[Ky1eb88]

Works fine for me so I can't reproduce or diagnose your error. Are any of the cells in column E and/or H merged?

Sorry like I said this is not my sheet some cells are merged, I don't need this to start until E11 and H10... Thanks for the help thus far!

 

[JoeMo]

Sorry like I said this is not my sheet some cells are merged, I don't need this to start until E11 and H10... Thanks for the help thus far!

Always a good idea to inform of merged cells in your initial post!! That's probably the source of the error you are getting. You can fix this by testing for merged cells in the code, possibly noting their addresses in a string variable or an array variable, unmerging them to lock or unlock as required and then re-merging them.

Are the merged cells necessary? If not, I would encourage you to remove all merges in the range of interest.

You can edit the code to start at E11.