Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
Application.EnableEvents = False
If Target.Address = "$A$1" Then
Me.Unprotect
Range("B1").Locked = False
If Target.Value = "Good" Then
Range("B1").Value = Range("A2").Value
Range("B1").Locked = True
End If
Me.Protect
End If

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"OrientalPearl" wrote in message
oups.com...
Hi everyone,

I am trying to apply a conditional formula to a cell B1. Basically what
will happen is that

If A1 = "Good" Then
B1 = A2
//then lock B1
Else
//make B1 editable


I have done the first step using IF(A1 = "Good", A2, "") [not sure
about "" is suitable though] and setting cell property - tick 'Locked'
option. But how can the lock be released in the Else situation?

I wonder the neat way to do this is to code this in VB(similar to the
syntax above) rather than using Excel menu tools. But how can it make
sure that any changes to A1 will lead to INSTANT adjustment(To make
sure it happens IMMEDIATELY, should I put such a piece of code at a
certain place?) to B1, i.e. (either =A2 and then locked, or simply make
B1 editable as a normal cell).

Sorry Im relatively new to excel macro and the question above is
probably quite basic. Appreciate help of any kind.

Regards
Frank