+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    09-04-2011
    Location
    Prilep, Macedonia
    MS-Off Ver
    Excel 2003
    Posts
    1

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

    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.

  2. #2
    Registered User
    Join Date
    08-27-2011
    Location
    South Carolina, USA
    MS-Off Ver
    Excel 2007
    Posts
    3

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

    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.

  3. #3
    Registered User
    Join Date
    09-14-2011
    Location
    Oregon
    MS-Off Ver
    Excel 2007
    Posts
    2

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

    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
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-14-2011
    Location
    Oregon
    MS-Off Ver
    Excel 2007
    Posts
    2

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

    figured it out. thanks anyway.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1