Programmatically setting Conditional Formatting to a range of cells

[Andrew]

I wish to programmatically set the conditional formatting of a range of
cells to highlight those cells that are locked.

I can readily do this 'manually' by highlighting a range of cells (say
A1:A7) and then entering a conditional format formula of
=CELL("protect",A1) - this assumes that when selecting the range A1:A7 the
first cell selected was A1. Excel automatically updates the second
parameter of the cell function so that, for example, the conditional format
formula in cell A7 is =CELL("protect",A7).

Programmatically in VB I can use the following (determined by recording a
macro as I did the above).

1- Range("A1:A7").Select
2- Range("A1").Activate 'is this necessary?

3- Selection.FormatConditions.Delete

4- Selection.FormatConditions.Add Type:=xlExpression, Formula1:=
"=CELL(""protect"",A1)"
5- Selection.FormatConditions(1).Interior.ColorIndex = 24

However, what I am not clear about is how to deal with a named range, such
as "Marks". The first statement above becomes:

1- Range("Marks").Select

How do I need to modify the reference to A1 in lines 2 and 4?

 

[Guest]

Sub confor()
For Each r In Range("happy")
ra = r.Address
r.FormatConditions.Delete
r.FormatConditions.Add Type:=xlExpression,
Formula1:="=CELL(""protect""," & ra & ")"
r.FormatConditions(1).Interior.ColorIndex = 24
Next
End Sub

seems to work. Remember that TRUE means the cell is LOCKed. Protection
comes only if the workbook is protected. Be careful of the wrap.

 

[Steve Yandl]

Sub SetCondFormatMarks()
Dim rngCell As Range
For Each rngCell In Range("Marks")
If rngCell.Locked Then
With rngCell
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=CELL(""protect"",A1)"
.FormatConditions(1).Interior.ColorIndex = 24
End With
End If
Next rngCell
End Sub

 

[Steve Yandl]

Andrew,

The sub I posted above only acts on the locked cells in the range "Marks".
My original thought was to just find the locked cells and change their color
with the sub and skip the conditional formatting but then I had it assign
the conditional formatting and failed to pull out the If..Then structure I'd
started with. Based on what you actually asked to do, the sub posted by
Gary's student makes more sense.

Steve

 

[Andrew]

I wish to programmatically set the conditional formatting of a range
of cells to highlight those cells that are locked.

I can readily do this 'manually' by highlighting a range of cells (say
A1:A7) and then entering a conditional format formula of
=CELL("protect",A1) - this assumes that when selecting the range
A1:A7 the first cell selected was A1. Excel automatically updates
the second parameter of the cell function so that, for example, the
conditional format formula in cell A7 is =CELL("protect",A7).

Programmatically in VB I can use the following (determined by
recording a macro as I did the above).

1- Range("A1:A7").Select
2- Range("A1").Activate 'is this necessary?

3- Selection.FormatConditions.Delete

4- Selection.FormatConditions.Add Type:=xlExpression, Formula1:=
"=CELL(""protect"",A1)"
5- Selection.FormatConditions(1).Interior.ColorIndex = 24

However, what I am not clear about is how to deal with a named range,
such as "Marks". The first statement above becomes:

1- Range("Marks").Select

How do I need to modify the reference to A1 in lines 2 and 4?

Thank you to "Garry's Student" and "Steve Yandl", both of whom used a loop
to solve the problem, which I was trying to avoid as 'inefficient'. After
posting my question I noticed an earlier post in this group (Conditional
Number Format - posted 11/11/07 01:43) which uses RC to refer to the current
cell.

Range("Marks").Select
Selection.FormatConditions.Delete

Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=CELL(""protect"",RC)"
Selection.FormatConditions(1).Interior.ColorIndex = 24

This appears to work as I require. However, I'm not too clear about how RC
'works' in this context. Can anyone offer any information on its use
please?

 

[Bill Renaud]

<<This appears to work as I require. However, I'm not too clear about how
RC 'works' in this context.>>

In the line:
Formula1:="=CELL(""protect"",RC)"

.... RC is "R1C1" notation that means "use the same cell" (same Row, same
Column). The format condition for each cell ends up referring to the
"locked" property of itself to determine what color it should be. So if the
cell is locked, it is colored pale violet, if it is not locked, then the
color is not filled.

Choose one of the cells in your range named "Marks", then select the
Format|Conditional Formatting command. Condition 1 will be shown for that
cell, and you will see that the "RC" has been converted to a normal A1
style reference.

Neat!!!

 

[Bill Renaud]

I forgot to add that this code works only as long as the worksheet has NOT
been protected yet. If the worksheet has been protected, then an error will
be raised.

I rewrote the routine slightly as:

'----------------------------------------------------------------------
Public Sub FormatMarksRange()
Dim wsMarks As Worksheet
Dim blnProtected As Boolean
Dim rngMarks As Range

Set rngMarks = Range("Marks")
Set wsMarks = rngMarks.Parent
blnProtected = wsMarks.ProtectContents

If blnProtected Then wsMarks.Unprotect

With rngMarks
.FormatConditions.Delete

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=CELL(""protect"",RC)"
.FormatConditions(1).Interior.ColorIndex = 24
End With

If blnProtected Then wsMarks.Protect
End Sub

 

[Andrew]

I forgot to add that this code works only as long as the worksheet
has NOT been protected yet. If the worksheet has been protected, then
an error will be raised.

I rewrote the routine slightly as:

'----------------------------------------------------------------------
Public Sub FormatMarksRange()
Dim wsMarks As Worksheet
Dim blnProtected As Boolean
Dim rngMarks As Range

Set rngMarks = Range("Marks")
Set wsMarks = rngMarks.Parent
blnProtected = wsMarks.ProtectContents

If blnProtected Then wsMarks.Unprotect

With rngMarks
.FormatConditions.Delete

.FormatConditions.Add Type:=xlExpression, _
Formula1:="=CELL(""protect"",RC)"
.FormatConditions(1).Interior.ColorIndex = 24
End With

If blnProtected Then wsMarks.Protect
End Sub

Excellent. Thank you, Bill, for your clear explanation of RC and your
suggested enhancements to my routine. This is clearly the way to go rather
than an iterative solution.