formating cells from IF statement

[Anner]

I want to protect a given cell if the contents of another cell is "Conf". I
can record a macro to Format->Cell->Protection->Locked, but how do I combine
that with the conditional IF? Thanks very much!

 

[JLGWhiz]

I don't believe you can protect just one cell the way you described. You
have to protect the entire sheet and then unlock those cells that you want to
allow editing in.

 

[Rick Rothstein]

Here is a different approach for you to consider. You didn't tells us
anything about your layout, so I made the assumption that A1 was the cell
you wanted protected if C3 contained the text "Conf" (change them to the
cells you are actually using. Put this code in the worksheet's code window
(right click that worksheet's tab and select View Code)...

'***************** START OF CODE *****************
Dim OldValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C3").Value = "Conf" And Target.Address = "$A$1" Then
On Error GoTo Done
Application.EnableEvents = False
MsgBox "That cell cannot be changed if A1 equals ""Conf""!"
Target.Value = OldValue
Target.Select
Else
'
' Your Change event code, if any, goes here
'
End If
Done:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then OldValue = Target.Value
'
' Your SelectionChange event code, if any, goes here
'
End Sub
'***************** END OF CODE *****************