+ Reply to Thread
Results 1 to 5 of 5

Lock a range of cells base on current date relative to cell reference

  1. #1
    Registered User
    Join Date
    03-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    10

    Lock a range of cells base on current date relative to cell reference

    Hi,

    I want to lock the cells in in column B and C dependent on the date in Column A -10, relative to today’s date. I want this to be automatic upon opening the sheet.
    Column A has the date Range A5:A16 starting with 1/1/15.
    Column B & C are manual entry (unlocked cells) Range B5:C16
    As the months progress I want to lock the Cells in column B & C

    Example 1: if Today() = 4/9/15 then Cells B5:C8 are locked, but Cells B9:C16 are still unlocked.

    Example 2: If Today() = 4/21/15 then Cells B5:C9 are locked, but Cells B10:C16 are still unlocked

    See attached sheet excerpt to demonstrate what I'm trying to do.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Lock a range of cells base on current date relative to cell reference

    I'm not sure I understand your examples. I thought you wanted to lock the previous months
    but in your examples, you are mentioning 2 dates in April and you want to lock different cells.
    I'm a bit lost.
    But if I did understood you right, the attached workbook should be OK for you.
    Otherwise, you could probably modify the macro so it works like you want.
    If not, get back here and we could work something else.
    Regards
    Attached Files Attached Files
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Registered User
    Join Date
    03-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    10

    Re: Lock a range of cells base on current date relative to cell reference

    Hi
    Thank you, your code is close to what I'm looking for: there is an error in the loop, in the code. It does not seem to stop.

    Scenerio:
    Cells B5:C16 are unlocked, all other cells are Locked, Sheet1 is protected.
    Unprotect sheet 1
    Start: IF(Today()>(A5-10),LockCell,Nothing)
    Continue until: IF(Today()>(A16-10),LockCell,Nothing)
    Protect Sheet 1.
    See Attached sheet for example of the run through on Rows 19 – 31.
    Attached Files Attached Files

  4. #4
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Lock a range of cells base on current date relative to cell reference

    try this macro:
    Please Login or Register  to view this content.
    Be sure not to put any other contents in column A but the Dates.

  5. #5
    Registered User
    Join Date
    03-20-2015
    Location
    USA
    MS-Off Ver
    2010
    Posts
    10

    Re: Lock a range of cells base on current date relative to cell reference

    Works Great, thank you for all your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 01-03-2013, 12:25 AM
  2. lock all cells before current date (Dates are in Column)
    By Sanoj in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-07-2012, 12:36 PM
  3. Replies: 3
    Last Post: 03-07-2012, 11:04 AM
  4. check if a range is blank relative to current cell in vba
    By sark666 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2009, 10:37 PM
  5. [SOLVED] Can I lock cell contents after current date is past?
    By Excel User Greg in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-16-2006, 02:50 PM

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