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.

lock range based on values loop
 

Still not clear as to your desires.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You send a clear explanation of what you want
3. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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.

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.

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.