Locking cells

EDMONd

New Member
Joined
Jan 19, 2005
Messages
30
I am trying to lock cells E10-E40 untill cell J4 has an entry greater than 3. I sure could use some help.
Thanks
EDMONd
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, Edmond,

WELCOME to the Board !!!

Really locking or is data validation good enough?

select your range
menu data / validation
choose custom
type in the formulabox
=$J$4>3

is this OK for you?

kind regards,
Erik

(EDIT: just saw it was you first post, so added "WELCOME to the Board !!!")
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
If the cells are locked to start with:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    If Target.Address <> "$J$4" Then GoTo exitMe
    If Target.Value > 3 Then
        ActiveSheet.Unprotect
        Range("E10:E40").Locked = False
    End If
exitMe:
    Application.EnableEvents = True

End Sub
should do it. If they aren't, you'd have to incorporate that. Post back if you need some help with that. Hope that helps!
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237

ADVERTISEMENT

Press Alt-F11 to open the VBE.
Press Control-R to open the Project Explorer.
Click "Microsoft Excel Objects" for the file you're working on.
Double-click the sheet where you need this to work.
Open the Code pane with F7.
Paste the above code in.
Press Alt-Q to close the VBE and return to Excel.

HTH
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
important questions if you would use code for this
is J4 a formula ?
can the value go down again ?

YES = other code needed
(but I wouldn't use code if it can be easily done without)

kind regards,
Erik
 

EDMONd

New Member
Joined
Jan 19, 2005
Messages
30

ADVERTISEMENT

J4 is a cell.
 

EDMONd

New Member
Joined
Jan 19, 2005
Messages
30
FYI I have been told many times that I shouldnt try to get tech help online. I am rather slow on the uptake.
:) EDMONd
 

Forum statistics

Threads
1,148,525
Messages
5,747,206
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top