Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock range based on values loop
I have a range "rows F$12:F$60" and if F4 (which is the sum of the range) is = than the control "$C$4" then the cells format red and then I want that range to lock. This is repeated up to column CW i.e. for range G$12:G$60 if G$4 is =$C$4 the range is locked, etc, etc, Looking through some of the previous examples I imagine that I would have to set some sort of loop for each column that increments but I dont know how. I previously asked to try to get it to lock based on conditional formatting but the response was that this was not possible. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock range based on values loop
If I understand what you want correctly, you can do this without any
programming at all. First, unprotect your worksheet. Next, if this is not already the case, remove the Lock from C4 (Format/Cells from the menu bar, select the Protection tab and uncheck the Locked check box). Next, select the range F12:CW60 (you can do this quickly by typing F12:CW60 in the Name Box - the empty field to the left of the Formula Bar), then click Format/ConditionalFormatting from the menu bar, then select "Formula Is" from the first drop down and copy/paste the following into the blank field that appears next to it... =AND(F12<"",F12=$C$4) then click the Format button, select the Patterns tab and pick the color you want to change the cells to when they equal C4 from the chart, then OK your way back to the worksheet. I'm assuming the range F12:CW60 already has its cells Locked check box checked (Format/Cells from the menu bar, select the Protection tab)... if not, then do it. Okay, now protect the worksheet again. As cells match the value in C4, they will be turned to the color you set when you Conditionally Formatted them but your user will not be able to change the contents of F12:CW60. -- Rick (MVP - Excel) "fishy" wrote in message ... I have a range "rows F$12:F$60" and if F4 (which is the sum of the range) is = than the control "$C$4" then the cells format red and then I want that range to lock. This is repeated up to column CW i.e. for range G$12:G$60 if G$4 is =$C$4 the range is locked, etc, etc, Looking through some of the previous examples I imagine that I would have to set some sort of loop for each column that increments but I dont know how. I previously asked to try to get it to lock based on conditional formatting but the response was that this was not possible. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock range based on values loop
I have two conditions already in place:
Formula is =(I$4=$C$4) - pattern = Red Formula is =(I$4=$C$4-($C$4*0.25)) - Pettern = Amber. (I$4is the sum of the range) The sheet range is unprotected but the range is unprotected. How could you explanation be amended to allow the range to lock? Richard "Rick Rothstein" wrote: If I understand what you want correctly, you can do this without any programming at all. First, unprotect your worksheet. Next, if this is not already the case, remove the Lock from C4 (Format/Cells from the menu bar, select the Protection tab and uncheck the Locked check box). Next, select the range F12:CW60 (you can do this quickly by typing F12:CW60 in the Name Box - the empty field to the left of the Formula Bar), then click Format/ConditionalFormatting from the menu bar, then select "Formula Is" from the first drop down and copy/paste the following into the blank field that appears next to it... =AND(F12<"",F12=$C$4) then click the Format button, select the Patterns tab and pick the color you want to change the cells to when they equal C4 from the chart, then OK your way back to the worksheet. I'm assuming the range F12:CW60 already has its cells Locked check box checked (Format/Cells from the menu bar, select the Protection tab)... if not, then do it. Okay, now protect the worksheet again. As cells match the value in C4, they will be turned to the color you set when you Conditionally Formatted them but your user will not be able to change the contents of F12:CW60. -- Rick (MVP - Excel) "fishy" wrote in message ... I have a range "rows F$12:F$60" and if F4 (which is the sum of the range) is = than the control "$C$4" then the cells format red and then I want that range to lock. This is repeated up to column CW i.e. for range G$12:G$60 if G$4 is =$C$4 the range is locked, etc, etc, Looking through some of the previous examples I imagine that I would have to set some sort of loop for each column that increments but I dont know how. I previously asked to try to get it to lock based on conditional formatting but the response was that this was not possible. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need sum of top values in a range based on criteria | Excel Worksheet Functions | |||
Cond. Format Data Bars of range based on values of another range | Excel Worksheet Functions | |||
Sum values in columns based on values in named range | Excel Worksheet Functions | |||
Populating a cell based on a range of values | New Users to Excel | |||
sum the values of a range based upon multiple ranges and criteria | Excel Worksheet Functions |