Locking cells using VBA based on other cell values

[Evil Red Smurf]

Hi,
I'm trying to lock cells in a column on an excel spreadsheet depending on
the (date) value in another cell on the same row. For example, I'm thinking
of it like this:
If range("C9").value > today()
Then range("G9").lock = false
Else range("G9").lock = true
End if

However I want to do it for all rows between 9 and 400 whenever the sheet is
accessed. Can anyone help?

Many thanks,

 

[incre-d]

1) Cells are automatically locked by default, but you have to protect the
workbook to make the locking work.

2) You start off with all cells locked and the worksheet unprotected.

To answer your question

theSheet.unprotect
Range("9:400").locked = true
theSheet.protect

 

[Bob Phillips]

You have a response in VBAExpress.!

 

[Evil Red Smurf]

Sorry I don't think I explained myself clearly previously.

I have a spreadsheet which is ready for data to be entered into G9:G400. I
have protected the spreadsheet with a password and unlocked G9:G400. What I
would like to do is if a date value in each of the cells in c9:C400 is
greater than today's date then the corresponding cell in column G should
remain unlocked and users be able to enter data into it. However if the date
value in C9:C400 is equal to or prior to todays date then the correponding
cell in column G should be locked.

Apologies again for not explaining that more clearly previously, if you
could help I would be most grateful.

 

[Lainie]

Hi,

I need something very similar. I need to interagate cells D8 through D69
and if there is an X in one of those cells then I need to unlock the cell in
column E so if D8 has an X then I need to unlock E8, if D9 has an X then I
need to unlock E9 etc. I am not sure how to find the response you noted
below. I am working very trial and error at adding bits of VBA code to my
spreadsheets to make them more user friendly. Thank you for your help.