+ Reply to Thread
Results 1 to 4 of 4

Lcok range of cells based on the value of other cells

  1. #1
    Forum Contributor
    Join Date
    06-25-2012
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    201

    Lcok range of cells based on the value of other cells

    hi.. i'm dealing with a question here...

    I have a worksheet where i have 2 collumns, collumn D and E.
    The data starts in D10 and goes down to D1000. On D i can have all king of data but in collumn E all i can have is "A" ou nothing "". So what i need is if in cell E12="A" then D12 will become unlocked. If E12 is empty "", then D12 should remain locked. And this for all range of D10:D1000 and E10:E1000.
    The password for the sheet is: "nocoments"

    I came across with some code after researching in google, but its only for one cell and not a range... for example:
    Please Login or Register  to view this content.
    This code is for one cell only, but i need something similar for the entire range.. .if E10 is A then D10 is unlocked, if E11 is A then D11 is unlocked... and so on....

    Please can someone help?

    Thank you.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,914

    Re: Lcok range of cells based on the value of other cells

    Hi there,

    Insert the following code into the VBA CodeModule of the worksheet which contains the cells you want to Lock/Unlock:

    Please Login or Register  to view this content.

    Run the "ProcessAllCellsInRange" routine just once - this should Lock/Unlock (as appropriate) all cells in the Range "D10:D1000"

    After this has been done, entering/deleting the character "A" (uppercase only) in any cell in the Range "E10:E1000" should Unlock/Lock the corresponding "D" cell.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Forum Contributor
    Join Date
    06-25-2012
    Location
    Porto, Portugal
    MS-Off Ver
    Excel 2010
    Posts
    201

    Re: Lcok range of cells based on the value of other cells

    @Greg M

    Thank you for your response to my question. I tried your code and it works really well. I just though that i had to run the code each time i copy/past data to the sheet where the code runs, but no. I rus once, and everytime i add new date via manual input or copy/paste it will update the cells to lock/unlock auto. Really helpfull. I just did a small change to the code where it shows "sheet1" i changed to my sheet name and -1 i changed to -3 because it was no longer the left collumn i was dealing anymore. I works like a charm.
    Many thanks.

    Regards.

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,914

    Re: Lcok range of cells based on the value of other cells

    Hi again,

    Many thanks for your feedback - I'm very pleased that I was able to help.

    Regards,

    Greg M

+ 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. [SOLVED] Formula Needed to count a range of cells based on criteria in 2 other cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-01-2013, 04:06 PM
  2. [SOLVED] Macro needed to clear cells in range based on value of cells in another range
    By msmith7113 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2013, 12:32 AM
  3. Copy range of cells to other cells based on first cells value
    By bluestarCVO in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2013, 10:02 AM
  4. Replies: 4
    Last Post: 01-03-2013, 12:25 AM
  5. Replies: 3
    Last Post: 09-13-2012, 03:25 AM

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