Locking cells based on criteria

[davidhall80]

I would like to lock and gray out cells in rows 8 to last row where the weekending date is less than cell E1. That way once the week passes, people will not have the chance to change past data. However, if I change the weekending date and it is less than the date of the header row, those cells have to go back to being unlocked and not gray anymore. Thanks in advance.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Current	week	ending	date	08-09-08
2
3
4	Please	enter	weekending	projections
5
6
7
8	06/07/08	06-14-08	06-21-08	06-28-08	07-05-08	07-12-08	07-19-08	07-26-08	08-02-08	08-09-08	08-16-08
9	6	7	7	5	1	5	5	3	9
10	1	4	0	6	1	8	0	2	0
11	6	1	2	9	6	1	6	4	4
12	9	7	5	1	7	4	4	5	0
13	7	7	0	6	1	7	6	7	4
14	0	4	1	8	3	6	3	3	5
15	4	6	9	2	2	2	1	9	1
16	4	4	8	8	7	9	0	1	1
17	4	8	0	1	1	6	3	8	8
18	2	5	2	6	4	7	8	1	6
19	7	8	5	3	1	9	9	2	0
20	8	2	9	2	5	5	1	3	3
21	5	7	9	9	6	8	7	0	2
22	9	7	1	3	3	3	9	3	6
23	5	4	1	2	2	9	9	5	2
24	9	7	5	9	0	5	3	8	2
25	3	5	7	5	5	9	3	0	5
26	4	3	7	4	4	6	3	9	7
27	9	6	5	3	9	7	6	9	2
28	1	4	3	8	8	9	2	1	0
29	5	1	0	6	0	4	1	8	7
30	9	3	2	8	6	2	6	5	7
31	2	8	0	5	9	8	4	8	2
32
Sheet1

 

[Smitty]

This should do it for you:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range</SPAN><br>        Set rng = Target.Parent.Range([A8], Cells(Columns.Count))<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Value < <SPAN style="color:#00007F">Date</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                ActiveSheet.Unprotect "bigdog"<br>                    Range(Target.Address, Cells(Rows.Count, Target.Column).End(xlUp)).Locked = <SPAN style="color:#00007F">True</SPAN><br>                ActiveSheet.Protect "bigdog"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that you'll need to unlock the input cells first.

HTH,

 

[davidhall80]

what do you mean unlock & input cells?

 

[Smitty]

what do you mean unlock & input cells?

Range A8:IV31 (or further down) will need to be unlocked (Cells-->Protection-->Uncheck Locked) prior to using the code, otherwise the sheet will be protected with all locked cells and you won't be able to enter anything.

By default all cells on a worksheet are set to Locked status. If you want to use worksheet protection you need to unlock the cells with which you want to interact first.

 

[davidhall80]

That explains the error the 1st time around. Thanks a ton Smitty. Works like a charm...