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

 

[JoeMo]

No B would be editable

Do you mean B would be editable or B would not be editable?

 

[Ky1eb88]

Do you mean B would be editable or B would not be editable?

Sorry, B is editable, only cells in A would be "locked."

 

[JoeMo]

Sorry, B is editable, only cells in A would be "locked."

OK here's a worksheet change macro you can use.
First, on your worksheet unlock all the cells in columns A & B (select both columns then Home>Cells>Format and click on Lock Cell. Be sure you have allowed users to select both locked and unlocked cells when you have protected the sheet.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

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

After you have installed the code, you can start it by entering a value in any B cell (if there is already a value there just enter that same value again). That's it, your A cells that equal the value in the B cell one row above will all be locked automatically. Or unlocked after being locked if the B companion should be changed so the value in A is no longer equal to its B companion.
Be sure to put your password into the code where indicated by the comment.

 

[Ky1eb88]

OK here's a worksheet change macro you can use.
First, on your worksheet unlock all the cells in columns A & B (select both columns then Home>Cells>Format and click on Lock Cell. Be sure you have allowed users to select both locked and unlocked cells when you have protected the sheet.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

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

After you have installed the code, you can start it by entering a value in any B cell (if there is already a value there just enter that same value again). That's it, your A cells that equal the value in the B cell one row above will all be locked automatically. Or unlocked after being locked if the B companion should be changed so the value in A is no longer equal to its B companion.
Be sure to put your password into the code where indicated by the comment.

Thank you! I will try this later!!

 

[Ky1eb88]

OK here's a worksheet change macro you can use.
First, on your worksheet unlock all the cells in columns A & B (select both columns then Home>Cells>Format and click on Lock Cell. Be sure you have allowed users to select both locked and unlocked cells when you have protected the sheet.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

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

After you have installed the code, you can start it by entering a value in any B cell (if there is already a value there just enter that same value again). That's it, your A cells that equal the value in the B cell one row above will all be locked automatically. Or unlocked after being locked if the B companion should be changed so the value in A is no longer equal to its B companion.
Be sure to put your password into the code where indicated by the comment.

I get a runtime error 1004
Unable to set the lock property of a range class.
Does this mean lock will not work in my situation?

I did have to change the columns this applies to to columns E and H as opposed to A and B

 

[JoeMo]

I get a runtime error 1004
Unable to set the lock property of a range class.
Does this mean lock will not work in my situation?

I did have to change the columns this applies to to columns E and H as opposed to A and B

Post the modified code you are using.

 

[Ky1eb88]

Post the modified code you are using.

I highly doubt I did it right but.. here it is:

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("E1:E" & lR)) Is Nothing Then
    Me.Protect Password:="", userinterfaceonly:=True  'Add your password between the " marks
    For Each c In Range("H2:H" & lR)
        If c.Value = c.Offset(-1, 1).Value Then
            c.Locked = True
        Else
            c.Locked = False
        End If
    Next c
End If
End Sub

 

[JoeMo]

I highly doubt I did it right but.. here it is:

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("E1:E" & lR)) Is Nothing Then
    Me.Protect Password:="", userinterfaceonly:=True  'Add your password between the " marks
    For Each c In Range("H2:H" & lR)
        If c.Value = c.Offset(-1, 1).Value Then
            c.Locked = True
        Else
            c.Locked = False
        End If
    Next c
End If
End Sub

You didn't do it justice. Could have saved time by posting the correct columns to begin with. I need to know:
Is column E the equivalent of column A in your original post? Is column H the equivalent of column B in your original post?

 

[Ky1eb88]

You didn't do it justice. Could have saved time by posting the correct columns to begin with. I need to know:
Is column E the equivalent of column A in your original post? Is column H the equivalent of column B in your original post?

That that is correct A=E and B=H, just thought it was easier to explain using A and B

 

[JoeMo]

That that is correct A=E and B=H, just thought it was easier to explain using A and B

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