A
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?
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?