Lock range based on conditional format colour.

[fishy]

I have used Dave Petersons response (pasted below) to locking ranges and this
works if I use a font but I have a spreadsheet that uses conditional
formatting that when
=(F$4>=$C$4) then the cell pattern (Cell shading) goes Red (The third colour
down on the extreme left of the standard pallet in Excel 2002).

I am trying to lock the range if any of the cells are in that particular
shade of red. I need this to run after any cell on the page is updated.

'Daves code---------------------------------------
Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .UsedRange
..Cells.Locked = True
End With

For Each myCell In myRng.Cells
If myCell.Font.ColorIndex = 5 Then
myCell.Locked = False
End If
Next myCell

End Sub
----------------------------------------------------

 

[Joerg Mochikun]

VBA can't detect cell shadings set by conditial formatting, so there is no
way to lock cells depending on their (conditional) format. However you can
use the condition (F$4>=$C$4) to modify the macro and lock all cells
fulfilling the condition.

Joerg

 

[fishy]

Thanks but I am still what I would describe as a beginner, could you sketch
out an example code?

 

[Joerg Mochikun]

OK, only a sketch. Below macro assumes that you have selected an area in
your worksheet. When you run the macro, it will lock all cells in the
selection and unlock those that meet a condition (in this case if cell value
is >= the value in C4).

Sub TestC4()
Dim myCell As Range
Selection.Cells.Locked = True
For Each myCell In Selection
If myCell >= Range("C4") Then
myCell.Locked = False
End If
Next myCell
End Sub

 

[fishy]

Thanks but still stuck

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.

R