Closed Thread
Results 1 to 27 of 27

Conditional grey-out and disabling of cells

  1. #1
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184

    Conditional grey-out and disabling of cells

    I need to find a way to do conditional formatting and disabling of a cell based on a value from another cell. For instance if A1=X,Y, or Z, then B1 will get grayed out and cell entry will be disabled. This is so that when skimming over the sheet, you can tell what cells in row B you need to put info in still, and if it's grayed you'll know you don't have to put anything there (and it won't let you). Didn't know if there was a way to do this. Any help is great, thanks!
    Last edited by jman0707; 10-10-2008 at 10:54 AM.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi there,

    See if putting this on the worksheet in question does the trick:

    Please Login or Register  to view this content.
    HTH

    Robert

  3. #3
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    That works great! Thanks a lot! Just a couple things that are giving me errors. I've attached the actual sheet just to make this easier. Instead of X,Y and Z that disable the cells, it's "DI" and "LTC" and these values are independently selected in the range E17:E91 on sheet 3. The cells to get greyed out and disabled are the corresponding F17:F91. So A1 and B1 are E17 and F17. I changed the code in the workbook to match this. Then the code can be written with no need for me to change it.

    1. It works when you select the first value, but then when you go from "DI" to "LTC" using the dropdown, or "LTC" to "DI" it gives me a runtime error and highlights the code below. The error happens either because the box is gray beforehand or it's because the sheet is protected beforehand, but the loop gets broken. (I tried coloring the F17 box gray with no value in E17, and it won't let me select ANY value). It also doesn't work if the sheet is protected beforehand (the one I'm working on will be). So some cells on the sheet are locked and some aren't, and the sheet is protected, and it must stay like that no matter what happens with this conditional formatting. Here's what the debugger highlights when the sheet is protected and you select "DI" or "LTC":

    Please Login or Register  to view this content.
    And if the sheet is protected and you select anything other than "DI" or "LTC" it unprotects the sheet (which is I need to be password protected and not prompt people to enter a password.) So maybe I lock all cells in column B and it conditionally unlocks them if the value is not "DI" or "LTC"???

    2. And so far only E17 and F17 work, but I need this for a whole range of numbers, so example if F17 = "DI" or "LTC", E17 is greyed and disabled. Same for F18 and E18, E19 and F19 etc. on down the range, so I wonder if we can tweak it. These will need to act independently for each row. And if there is something already typed in a cell in column F, it needs to be cleared out, greyed, and disabled when "DI" or "LTC" is selected.

    Thanks for your help Trebor76!

    I've unprotected the worksheet in question. It will need to be password protected in the end though. Thanks!
    Attached Files Attached Files
    Last edited by jman0707; 10-02-2008 at 05:45 PM.

  4. #4
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi there,

    I've adjusted the following to incorporate your needs (hopefully). Just note that I've set the password as "YourPasswordHere" so you can easily see where to change it later:

    Please Login or Register  to view this content.
    Note too that the code will also execute if a "Z" is entered in column E from cell 17 on and I don't have Excel 2007.

    HTH

    Robert

  5. #5
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    IT'S SO CLOSE! It works really great, just a couple more small things, and if I knew anything about programming I'd tweak them myself, but i don't!

    1. When you select anything other than "DI," "LTC," or "Z" it unlocks the whole sheet and then all cells on the worksheet are un-protected. Then when it re-protects, it forgets which cells were locked and which weren't, and leaves all cells unlcoked. Is there any way to get it to immediately lock again when one of those 3 values is selected? The sheet needs to stay locked at all times.

    2. And the other things is, let's say when you select a value in E17 such as "DI" and F17 get's greyed out and locked, then you move down and select "DI" for E18 and F18 gets locked, F17 re-opens. I need for all the greyed out cells to stay locked once their locked. It only locks the last cell that you work with. And i don't think I can manually lock column F because if the value IS NOT one of the 3 chosen, I need to be able to enter information still, and can't do that if the sheet is locked like it will be AND all the cells are locked. So I need all greyed out cells to stay locked, all non-greyed out cells to stay unlocked, and the sheet to always remain locked and password protected.

    And a couple other wierd quirks.

    3. Whenever I highlight at least one E cell and one F cell (even if they are blank) and hit "delete" it gives me an error message, "run-time error '13.'"
    4. Also, when I manually type in the values instead of using the drop down list, the wrong cell gets affected (ex. if I typed "DI" in E17, F18 would turn grey and lock. And vice versa with typing in a value other than the 3 chosen, the wrong cell turns white.

    5. And when I clear out a cell and it's blank, the corresponding cell stays gray. So maybe that goes along with the last error and when you type stuff in it gets messed up.

    6. And one other thing (not too important) is when I hit Ctrl^C to copy a cell in column E to paste in multiple other cells, it only copies once, making you hit Ctrl^C again instead of doing multiple pastes. This is last b/c it's not super important. If these few quirks get worked out it will be PERFECT! Hopefully it's not too much tweaking. It just needs to look very professional while at the same time being user friendly for the people inputting info. Thanks so much for all your help!
    Last edited by jman0707; 10-06-2008 at 04:14 PM.

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Sorry but I think this is beyond my limited capabilities

    Hopefully there's a more learned person here who'll be able to provide you a working solution.

  7. #7
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    OK, thanks a lot for your help Trebor76! I'll keep the code and hopefully continue to work on it. The protection is very vital to the sheet and that's the only reason I'm being so specific. But I really appreciate your help, have a good one!

  8. #8
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi there,

    One last try - let me know how the following goes (note my comments as well).

    Regards,

    Robert

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    YES!!! That is awesome! This is totally not above your skill level! It works great!

    The hard part is over. There are 2 little quirks that hopefully you can help me iron out.

    1. When I select more than one cell in the whole worksheet, I get the "runtime error 13; type mismatch" error. Even if there is nothing in the cell to begin with. And this goes for any 2 cells in the whole workbook. I need to at least be able to highlight across a row and press delete since all the data for each individual case is on the same row.

    2. As soon as I type anything else in a row where the cells been greyed and locked, it becomes unlocked. And when I double click on another cell in the row of a cell that's been disabled, it also becomes unlocked and turns white. If I type something in any other row, it's still fine.

    So that's it, I know it looks like a lot of fixes, but this really works awesome right now and I'm excited! So please let me know if you know how to fix these couple quirks and then it will be ready for users! Thanks for all yoru hard work Trebor76!
    Last edited by jman0707; 10-08-2008 at 05:38 PM.

  10. #10
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi jman0707,

    I'm excited!
    - me too as I think the following is it (I hope):

    Please Login or Register  to view this content.
    Kind regards,

    Robert

  11. #11
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    AWESOME! Wow, it's almost there! Sorry that this isn't over, but it's SOOO close! I'll display even more excitement when I can stop bugging you!

    Most of the delete errors are gone. But one instance where i get an error still is when I highlight multiple cells in COLUMN E ONLY. ALL other combinations work just fine, so that's sweet! I think it's just the error that stops the programming from working, hence the fact that it doesn unlock and whiten the cell. Hopefully it's a small fix since you got everything else to work.

    And the ONLY other thing that doesn't quite work is when I highlight multiple cells (any combination, but includes at least one "DI" or "LTC" or "Z" from column E, the text gets erased but the corresponding box in column F is still greyed and locked. It works just fine when I click on one cell at a time and use backspace.

    THAT'S it, it's almost there! Hopefully it's just a few quick tweaks to this already amazing code! Thanks Trebor76!

  12. #12
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    I can't see it working for a selection of cells as the code will have to check each cell the range and if any cell in that range is changed the code will start all over again - effectively creating a loop.

    I've therefore put a message in the following to inform the user that the code only works on an individual cell.

    HTH

    Robert

    Please Login or Register  to view this content.
    HTH

    Robert

  13. #13
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    You can do this with a combination of Validation and Condiditonal Formatting.
    Select B1 and set Validation to the custom formula
    =NOT(OR($A1="X",$A1="Y",$A1="Z"))
    Setting Conditional Formatting to the formula that is the negation of the above would provide the grey.
    Copy the formatting and Valitation to the rest of the cells of interest.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  14. #14
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Trebor76, I tried pasting this new code in there, and it gave me an error on the first attempt at putting "DI" or "LTC" in. So I reverted to the last one you sent me.

    Thanks mikerickson! What I'd like though, and what Trebor76 was helping me on is to also lock the cells in column F when something those values are typed in column E. It operates more professionally and I don't want error boxes showing up over and over when people try to enter information in those cells, so the programming doesn't even allow them to click on the cells. If there was a way to disable the pop up boxes that would be a good solution.

    For anyone reading, the second to last code is what I'm working with, and here are the specific things that aren't quite working.

    1. Highlighting multiple cells in column E using a click and drag, and hitting delete, I get a runtime error. But highlighting one at a time using control DOES WORK.

    2. Highlighting one cell in row E and one in row F using click and drag, get a runtime error. But again, using control and doing one cell at a time DOES WORK.

    3. Highlighting columns a to F using click and drag DOES work, but doing it from E to L doesn't.

    And highlighting multiple rown and columns DOES work.

    I just don't want users to have to sift through multiple error messages (either with validation or the delete button not working in this code). And I need them to be able to delete a complete row at a time, and not have to select a different value besides "DI" "LTC" or "Z" in order to unlock the corresponding cell, it needs to go away as soon as the value in row E goes away.

    Trebor76 has AWESOME code here, so if anyone has any ideas on how to tweak it to work that would be GREAT! Disabling auto fill for this worksheet would probably work, but then it would now allow mass data deletion. I know nothing about programming, so i'm just trying to help spark some insights! Thanks everyone!

    Justin
    Last edited by jman0707; 10-09-2008 at 11:18 AM.

  15. #15
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    Is there any possible way to get the program to recognize that "DI" and "LTC" are no longer there when using the DELETE key? I just don't know why using delete is different than backspace, should recognize that there's nothing there anymore either way. After a lot of testing, I think if we can fix that one thing it will be done. No other scenarios will really ever happen. Thanks!

    And one other thing. Is there a way do disable the VB message box that says "run time error?" It only gives one option anyway and that's to end. So maybe we can always have it "end" and not have the message box show up. The end result on the worksheet is already the desired result, it's just a nuisance to click on every time it comes up, and it also makes it look like it's not working right (which it is still).
    Last edited by jman0707; 10-09-2008 at 05:41 PM.

  16. #16
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523
    Hi there,

    Not sure what's happening but deleting a cell from the input range within Column E works fine for me - as did my previous post

    Though the following turns off error messages it doesn't solve any code problem i.e. you can copy cells down Column E but the code will stop running so the adjacent cells in Column F won't get locked / unlocked as would happen if just one cell was changed.

    HTH

    Robert

    Please Login or Register  to view this content.

  17. #17
    Forum Contributor
    Join Date
    09-30-2008
    Location
    Eau Claire, WI
    Posts
    184
    OK, thanks! I might give that a try. When the error occurs, it says "type mismatch" and highlights this code in the debugger:

    Please Login or Register  to view this content.
    Does that help at all? Just thought it might spark something!

    How do I paste the code to stop error messages in VB when I already have all that other code. I know I can't just paste it right after the other. Can you explain how to paste them in to get them to both work simultaneously?

    Do you have XL07? I'm not sure why it's not working for me. But at least we can stop the error messages and make the continuity a little better. Thanks again for everything, you are awesome!
    Last edited by jman0707; 10-10-2008 at 11:00 AM.

  18. #18
    Registered User
    Join Date
    02-11-2009
    Location
    Miami, FL
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Conditional grey-out and disabling of cells

    Hi, I have a similar situation that i could use help with. I have four tables in which the first two are identically paired and so are the second pair. The first table takes up the range D14:K16 and the second in range P14:W16 which makes the first pair. The 3rd and 4th tables are in ranges E19:J23 and Q19:V23 respectively. What I need to happen is if I enter either "SM" or "CM" in cell D14 then cell P14 gets greyed out. Inversely, if the data is entered in P14 then D14 needs to be greyed out. This should happen in any of the cells in either table range (i.e. D14-P14, F14-R14, I15-U15, G16-S16...)
    Would appreciate any help.

    Many thanks -

  19. #19
    Registered User
    Join Date
    04-26-2010
    Location
    Colorado
    MS-Off Ver
    Excel 2007
    Posts
    5

    Value in a cell "unprotects" specific cells

    I've come across a few variation, however, none that I can apply. I'm using Excel 2007 and what I'm trying to do is, invoke a macro that based on a number I input in a cell, will in turn, unprotect "specific" other cells. I'm not too concerned about it 're-protecting" in the end.
    Example:
    First of all, the worksheet in password protected in its entirety. When I input the number 2 in A1, I would like it to unprotect cells C1, E2, and F3. If I input the 3 in A1, I want to keep the previous cells (C1, E2, and F3), unprotected and have an additional C2, E3, and F4, unprotected as well. following suit, If I input the number 4 in cell A1, all the other previous cell are still unprotected with the additional cells C3, E4,and F5.
    All at the same time keeping the rest of the worksheet still protected.

    thanks

  20. #20
    Registered User
    Join Date
    01-14-2011
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    1

    Exclamation Re: Conditional grey-out and disabling of cells

    Hi,

    I have a problem in excel pls help. My objective is to disable cells in each column indepent to Other columns. i.e if i select "No" in Cell B2 in Column B, Then Cell B3 needs to be grayedout and disabled, And also if i select "No" in Cell C2 in Column C, then Cell C3 need to be grayed out and locked. Which i want to continue like this for the entire sheet.

    Current status
    If i Select " Yes" in Cell B2 then cell B3 gets locked and grayed out which works perfectly as i want it, but when it is applied for Cell C2 and if i select No i get run time error 1004.

    Query
    Can the VBA work independent for the columns?

    What can be the solution for this ?

    The Code follows
    ==========================================
    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$2" Then
    If Target.Value = "No" Then
    Range("B3").Interior.Color = RGB(192, 192, 192)
    ActiveSheet.Cells.Locked = False
    Range("B3").Locked = True
    ActiveSheet.Protect
    Else
    ActiveSheet.Unprotect
    Range("B3").Interior.ColorIndex = xlNone
    End If
    End If
    If Target.Address = "$C$2" Then
    If Target.Value = "No" Then
    Range("C3").Interior.Color = RGB(192, 192, 192)
    ActiveSheet.Cells.Locked = False
    Range("C3").Locked = True
    ActiveSheet.Protect
    Else
    ActiveSheet.Unprotect
    Range("C3").Interior.ColorIndex = xlNone
    End If
    End If
    ===========================================

  21. #21
    Registered User
    Join Date
    05-19-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2003
    Posts
    2

    Unhappy Re: Conditional grey-out and disabling of cells

    Hi,
    I don't know a thing about VB , can anybody help me in managing my attendance and break sheet?

    I've uploaded an excel, what I want?

    1). If the value in C6 is PRESENT, then only I should be able to enter the details in respective cells against C6 till U6

    2). If anything except PRESENT is selected in C6, all the cells till U6 must be greyed out and disabled.

    This practice has to be followed till row 28, and this will be done everyday in a new sheet with the respective date.

    Please anyone do the coding for me and make the sheet for me, this might get me a promotion in my company.
    Please let me know if any other information is required from me.

    I really appreciate the help.

    Thank you
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    10-01-2012
    Location
    Bangalore
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Conditional grey-out and disabling of cells

    HI,

    I am able to disable(greyed) the cell but i have used evcvw function too over it as i'm working on SAP BPC...can u help me how to disable the function used in the cell too..this is my prog

    Private Sub Worksheet_Change(ByVal Target As Range)

    'If Target.Address = "$L$13" Then
    Range("L13").Select
    ActiveSheet.Unprotect
    If Target.Value = "Brand" Or Target.Value = "Sub Brand" Then
    Range("$l$14").Interior.Color = RGB(192, 192, 192)
    ActiveSheet.Cells.Locked = False
    Range("$l$14").Locked = True
    ActiveSheet.Protect

    Else
    Range("L14").Select
    ActiveSheet.Unprotect
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    Range("$l$14").Locked = False

    End With
    ActiveSheet.Protect
    End If
    ' End If

    End Sub

  23. #23
    Registered User
    Join Date
    04-15-2020
    Location
    Thousand Oaks, US
    MS-Off Ver
    2010
    Posts
    1

    Re: Conditional grey-out and disabling of cells

    Hello Trebor76,

    I would appreciate if you could help me. I use the same code but I am not a coder so I changed the variables based on my file but when I choose the drop-down for which another cell should get locked it doesnt do it. any thought? Thank you

  24. #24
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional grey-out and disabling of cells

    Quote Originally Posted by annahanoom View Post
    Hello Trebor76,

    I would appreciate if you could help me. I use the same code but I am not a coder so I changed the variables based on my file but when I choose the drop-down for which another cell should get locked it doesnt do it. any thought? Thank you
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  25. #25
    Registered User
    Join Date
    04-05-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    2

    Post Re: Conditional grey-out and disabling of cells

    Hey,
    I want my column L1 and M1 to get locked based on the value entered in K1. I want this to happen for all rows like ..
    K1 --> L1 M1
    K2 --> L2 M2
    K3 --> L3 M3... and so on..
    How can I do this..!
    Thanks

  26. #26
    Registered User
    Join Date
    04-05-2021
    Location
    India
    MS-Off Ver
    2019
    Posts
    2

    Re: Conditional grey-out and disabling of cells

    Hey,
    I want my column L1 and M1 to get locked based on the value entered in K1. I want this to happen for all rows like ..
    K1 --> L1 M1
    K2 --> L2 M2
    K3 --> L3 M3... and so on..
    How can I do this..!
    Thanks

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Conditional grey-out and disabling of cells

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed 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