Lock range of cells according to comparison between two cells (>, <, =)

**Rondeil** · 09-04-2011, 04:52 PM

Working on a project i came to a problem that with my VB knowledge (not very great) i can not solve it.
What i wanted excel to do is:
When a value in one cell changes, according to the comparison of two cells (equal, larger than, smaller than), excel is supposed to lock certain range of cells. Confusing i know.. Here it is simpler with example.

We have:
One table with dates from 1st to 31st January for ex. and all in one range of cells A1:A31

One cell (let's call it TODAY) containing the current date, retrieved with the formula "today()"

Now. If TODAY contains the value of 15th of January.

Excel checks whether any of the values in the range A1:A31 are smaller than the value in the TODAY cell.

And if any of them are, it locks them and the cells next to them (for ex. from B1:B31) (protect sheet) so I can't change anything in them.

So when a certain date has passed, i want the cells containing values of any kind and therefor in a certain range with or without the date cell itself, get Locked.

I'm thankful for any help regarding this problem and any suggestion is appreciated. If I wasn't clear enough i can upload screen-shot with example of the actual sheet I'm working on, and try explain the problem better.

Rondeil.

**onrock** · 09-04-2011, 10:21 PM

to lock a range of cells, you would need to do 2 things: (1) lock the selected cells; (2) protect the sheet with a password. The code can be as follows:

Range("A2:B2").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

You also need to use loop for comparing 2 cells if there is a range of cells you need to go thru.

I hope this will get you started.

**ambie80b** · 09-14-2011, 01:03 PM

I have a similar problem: I have two cells per day of week where I want a user to put in the occupied start and end time for their business. I have a table below it that contains all the hours of the day. I want the the cells in the hours per day table outside of the occupied period per day to be protected when the user clicks the button. See attached excel sheet. Mostly I can't figure out how do I do the comparision in a macro.

Thanks for your help in advance!
Ambie