Loop depending on value problem

[rob nobel]

I need to lock columns C depending on whether Col M has a value in it.
The following is my attempt, but it's lacking somewhat.
I believe what I have will only work if the cells in col M should show true
or false but the cells in Col M have a $ value or nil value.

Public Sub LockIncomeCells()
On Error GoTo Oops
Dim Cell As Range
Sheet6.Unprotect
For Each Cell In Sheet6.Range("M5:M29")
With Cell
.Offset(0, -10).Resize(1, 2).Locked = .Value 'LOCK name &
Code
End With
Next Cell
Oops:
Sheet6.Protect
End Sub

Furthermore, as an alternative, is it possible to lock all cells (on
sheet6), in a range C5:L29, that are a particular colour (Red)?

Thank You,
Rob

 

[Frank Kabel]

Hi
try (not tested)

Public Sub LockIncomeCells()
On Error GoTo Oops
Dim Cell As Range
Sheet6.Unprotect
For Each Cell In Sheet6.Range("M5:M29")
With Cell
If .value <>"" then
.Offset(0, -10).Resize(1, 2).Locked = TRUE
end if
Next Cell
Oops:
Sheet6.Protect
End Sub

 

[rob nobel]

Thankyou very much Frank.
Just had to add an End With to the procedure and change value <>"" to value
<> 0, and it worked great.

Can you or someone help me with the second question, which is...
Furthermore, as an alternative, is it possible to lock all cells (on
sheet6), in a range C5:L29, that are a particular colour (Red)?

Rob

 

[Frank Kabel]

Hi Rob
sorry, I deleted your 'end with'. For your second question - I didn't
noticed as it was at the end of your posting - try the following

Public Sub LockIncomeCells()
On Error GoTo Oops
Dim Cell As Range
Sheet6.Unprotect
For Each Cell In Sheet6.Range("M5:M29")
With Cell
If .value <>0 then
.Offset(0, -10).Resize(1, 2).Locked = TRUE
end if
end with
Next Cell

For Each Cell In Sheet6.Range("C5:L29")
With Cell
'If .font.colorindex = 3 -> for the font color
If .Interior.colorindex = 3 '-> the fill
color/change the value as desired
.Locked = TRUE
end if
end with
Next Cell

Oops:
Sheet6.Protect
End Sub

 

[rob nobel]

Hi Frank. Thanks again. I'm having some problems with the colour part as
I'm not quite sure how it works so not sure how to amend it.
I presume the If .font.colorindex = 3 is the condition, so if the font in
that cell is = 3 then that cell will be locked.
Do you have a list of what numbers belong to what colours? Or is there an
easy way to find the colorindex?
Rob

 

[Frank Kabel]

Hi
your assumption is correct
For a list of colorindexes have a look at
http://www.mvps.org/dmcritchie/excel/colors.htm

 

[rob nobel]

Thanks again Frank!
Rob

Hi
your assumption is correct
For a list of colorindexes have a look at
http://www.mvps.org/dmcritchie/excel/colors.htm

 

[rob nobel]

Back again.
Frank, your procedure works fine. It seems that the problem is that if the
font colour is as a result of conditional formatting, then it doesn't work.
Any way round this?
Rob

 

[Frank Kabel]

Hi
yes if the color is created by conditionla format this won't work.
Excel treats this kind of color differently (the colorindex property
remains unchanged).
AFAIK beside checking the original conditions by yourself there is no
way to get this conditional format color

 

[Dave Peterson]

I would think checking the conditions yourself would be simpler, but Chip
Pearson has a technique at:

http://www.cpearson.com/excel/CFColors.htm

 

[rob nobel]

Thanks Dave, just having a quick look at the code on that page.....blimey!
I might try something else like format them via VBA instead of conditional.
Rob

 

[Dave Peterson]

A very wise choice. <vbg>

Thanks Dave, just having a quick look at the code on that page.....blimey!
I might try something else like format them via VBA instead of conditional.
Rob