Conditionally protecting a cell if it has a certain value

[wonky1]

Hi all, first post here. i'm Wonky1 from winkywankywoo (lol?), and although i've had a little experience with Excel, i'm a complete novice with vba.

Using Excel 2007 and Windows Vista, i've been trying to find a way of temporarily protecting a cell if it has a certain value, until a different cell has another certain value, for example:

A1 = "lemon"
A6 = "tiger"........

A6 then changes to "rhino", and now i want to hold A6 = "rhino" until A1 = "apple" or anything else apart from "lemon".

i'm hoping there's a way of coding to achieve this, but i'm stuck. i'd also like to apply "the formula" (if there is one) to a range of cells that behave in the same way as A6, so that individual cells in A6:A24 become temporarily fixed if they say "rhino" when A1 = "lemon".

This is a bit of code i've come up with so far, but it returns

Run-time error '424':
Object required

Novice!


Sub fruit()

If cell.A1 = "lemon" & cell.A6 = "rhino" Then
cell.A6.Locked = True

Else
cell.A6.Locked = False

End If

End Sub


Apologies if this has been covered elsewhere, but any help, guidance, or suggestion would really be appreciated.

Thanks for reading, and Thanks even more for any response

 

[VoG]

Something like this?

Sub fruit()
ActiveSheet.Unprotect
If Range("A1").Value = "lemon" & Range("A6").Value = "rhino" Then
    Range("A6").Locked = True
Else
    Range("A6").Locked = False
End If
ActiveSheet.Protect
End Sub

 

[wonky1]

Rapid response, VoG, thank you!

Your code looks good to me(?!), but it seems to lock the whole sheet, so i unlock by going

Review > Unprotect Sheet (in Changes tab),

and then all locks disappear.

Any other thoughts, please?

 

[VoG]

Cells are locked by default. Select the range of cells that you are working with, press CTRL + 1 then on the Protection tab untick Locked. The code should then work as you want.

 

[wonky1]

Hmmm, I'm still not behaving as i should be. Will play around this afternoon on a fresh sheet, possibly a double negative somewhere? Let you know in a day or two.

I really appreciate your expertise. Thanks again.

 

[wonky1]

Hi Peter and everyone,

i've found a way of logging when a cell gets populated by use of your code from

http://www.mrexcel.com/forum/showthread.php?t=512144


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
Target.Offset(, 1).Value = "yes"
Application.EnableEvents = True
End If
End Sub

That's very useful, thanks.

Can somebody please let me know if it's possible to reset the logged "yes" (as above) automatically when "ok" appears in another column, so that it's fresh and ready for next time? i guess this would be a circular process? If possible, it would help me manage my time a lot better.

Grateful for any help! Cheers!!

 

[VoG]

Try this: enter value in B and yes appears in C. Enter ok in D and C is cleared

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
    Application.EnableEvents = False
    Target.Offset(, 1).Value = "yes"
    Application.EnableEvents = True
ElseIf Target.Column = 4 Then
    If Target.Value = "ok" Then
    Application.EnableEvents = False
        Target.Offset(, -1).ClearContents
    Application.EnableEvents = True
    End If
End If
End Sub

 

[wonky1]

That's brilliant, Peter.... managed to adapt that perfectly for my needs.

Thank you so much - i'll buy you a beer