Excel 2007 : Lock cells with IF formula

[pansovic]

Is it possible that I lock cells based on the outcome of a formula? For example I have a formula in cell A1 and when the results is 5 than cell is blocked and nobody can change it but when the result is for example 8 than you can overwrite the formula.

[Richard Buttrey]

Hi,

The first point to bear in mind is that to lock cells you also have to have sheet protection switched on.

You can't lock or unlock cells with a formula but you could use the Sheet Change event
i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
    Sheet1.Unprotect
    If Range("A1") = 5 Then
        Range("A1").Locked = True
    Else
        Range("A1").Locked = False
    End If
    Sheet1.Protect
End Sub

Obviously you are going to have to unlock the cells which provide values for the A1 formula.

Regards

[pansovic]

Sorry for the (very) late reply though had to travel a lot and only now am back in "Excel mind".
I really have a problem with protecting my data. You suggested above that I need to switch on Sheet protection. Though than I can no longer us macro. I have a macro that insert me a line copying all the formulas of the above line with "ctrl+F"
So than I opt for Sharing the document so that at least I can see what others are doing but neither that is working as that I am using Pivot table.

Is there really no way that I can protect all my formulas and data?

[royUK]

If I follow what you are saying then your macro needs to unprotect the sheet, perform it's functions the re-protect the sheet as the example code does

[pansovic]

Using your code I receive error message and marked in yellow is "Sheet1.Unprotect". I already changed Sheet1 by name of the sheet though same error. My Excel version is Spanish though I have used other codes in English and they do are working.