Programmatically setting Conditional Formatting to a range of cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andrew - 11 Nov 2007 17:13 GMT

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?

Reply to this Message

Gary''s Student - 11 Nov 2007 19:56 GMT

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.

Signature

Gary''s Student - gsnu2007a

> I wish to programmatically set the conditional formatting of a range of
> cells to highlight those cells that are locked.
[quoted text clipped - 24 lines]
>
> How do I need to modify the reference to A1 in lines 2 and 4?

Reply to this Message

Steve Yandl - 11 Nov 2007 20:11 GMT

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

>I wish to programmatically set the conditional formatting of a range of
>cells to highlight those cells that are locked.
[quoted text clipped - 24 lines]
>
> How do I need to modify the reference to A1 in lines 2 and 4?

Reply to this Message

Steve Yandl - 11 Nov 2007 20:22 GMT

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

> Sub SetCondFormatMarks()
> Dim rngCell As Range
[quoted text clipped - 38 lines]
>>
>> How do I need to modify the reference to A1 in lines 2 and 4?

Reply to this Message

Andrew - 12 Nov 2007 10:04 GMT

> I wish to programmatically set the conditional formatting of a range
> of cells to highlight those cells that are locked.
[quoted text clipped - 24 lines]
>
> 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?

Reply to this Message

Bill Renaud - 12 Nov 2007 17:07 GMT

<<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!!!

Signature

Regards,
Bill Renaud

Reply to this Message

Bill Renaud - 12 Nov 2007 17:22 GMT

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

Signature

Regards,
Bill Renaud

Reply to this Message

Andrew - 13 Nov 2007 13:09 GMT

> 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
[quoted text clipped - 24 lines]
> 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.

Reply to this Message