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.
Hi there,
See if putting this on the worksheet in question does the trick:
HTH
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then If Target.Value = "X" Or Target.Value = "Y" Or Target.Value = "Z" Then Range("B1").Interior.Color = RGB(192, 192, 192) ActiveSheet.Cells.Locked = False Range("B1").Locked = True ActiveSheet.Protect Else ActiveSheet.Unprotect Range("B1").Interior.ColorIndex = xlNone End If End If End Sub
Robert
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":
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"???Range("B1").Interior.Color = RGB(192, 192, 192)
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!
Last edited by jman0707; 10-02-2008 at 05:45 PM.
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:
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.Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Column = 5 And ActiveCell.Row >= 17 Then If Target.Value = "DI" Or Target.Value = "LTC" Or Target.Value = "Z" Then ActiveSheet.Unprotect Password:="YourPasswordHere" With ActiveCell.Offset(0, 1) .ClearContents .Interior.Color = RGB(192, 192, 192) End With ActiveSheet.Cells.Locked = False ActiveCell.Offset(0, 1).Locked = True ActiveSheet.Protect Password:="YourPasswordHere" Else ActiveSheet.Unprotect Password:="YourPasswordHere" ActiveCell.Offset(0, 1).Interior.ColorIndex = xlNone End If End If End Sub
HTH
Robert
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.
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.
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
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
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:Originally Posted by annahanoom
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
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!
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
HTHPrivate Sub Worksheet_Change(ByVal Target As Range) Dim strMyPassword, strColumnChg As String Dim lngLastRow, lngRowChg As Long 'Set your desired password to this variable. strMyPassword = "YourPasswordHere" ActiveSheet.Unprotect Password:=strMyPassword lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row strColumnChg = Left(Target.Address(False, False), 1) lngRowChg = Val(Mid(Target.Address(False, False), 2, Len(Target.Address(False, False)) - 1)) 'Code will only work if: _ 1. The change has occurred in Column E _ 2. The change is from row 17 to the last row used in Column A (inclusive), and _ 3. A range (which Column E forms a part) has not been selected. If strColumnChg = "E" And _ lngRowChg >= 17 And _ lngRowChg <= lngLastRow Then If InStr(Target.Address(False, False), ":") > 0 Then MsgBox "The Worksheet_Change event is not coded to work on a range.", _ vbExclamation, "Case Type Change Editor" Exit Sub End If If Target.Value = "DI" Or _ Target.Value = "LTC" Or _ Target.Value = "Z" Then With Range("F" & lngRowChg) If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Locked = False If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .ClearContents If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Interior.Color = RGB(192, 192, 192) If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Locked = True End With Else With Range("F" & lngRowChg) If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Locked = False If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Interior.ColorIndex = xlNone End With End If Else Exit Sub End If ActiveSheet.Protect Password:=strMyPassword End Sub
Robert
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.
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.
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.
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
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo EndCode Dim strMyPassword, strColumnChg As String Dim lngLastRow, lngRowChg As Long 'Set your desired password to this variable. strMyPassword = "YourPasswordHere" ActiveSheet.Unprotect Password:=strMyPassword lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row strColumnChg = Left(Target.Address(False, False), 1) lngRowChg = Val(Mid(Target.Address(False, False), 2, Len(Target.Address(False, False)) - 1)) 'Code will only work if the change has occurred in Column E and from rows _ 17 to the last row used in Column A (inclusive). If strColumnChg = "E" And _ lngRowChg >= 17 And _ lngRowChg <= lngLastRow Then If Target.Value = "DI" Or _ Target.Value = "LTC" Or _ Target.Value = "Z" Then With Range("F" & lngRowChg) If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Locked = False If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .ClearContents If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Interior.Color = RGB(192, 192, 192) If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Locked = True End With Else With Range("F" & lngRowChg) If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Locked = False If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect Password:=strMyPassword End If .Interior.ColorIndex = xlNone End With End If End If ActiveSheet.Protect Password:=strMyPassword Exit Sub EndCode: End Sub
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
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
OK, thanks! I might give that a try. When the error occurs, it says "type mismatch" and highlights this code in the debugger:
Does that help at all? Just thought it might spark something!If Target.Value = "DI" Or _ Target.Value = "LTC" Or _ Target.Value = "Z" Then
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.
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 -
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
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
===========================================
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks