On Jun 26, 10:50 am, andy <a...@discussions.microsoft.com> wrote:
> after having recorded a macro containing 3 conditional formats, i retrieve
> the following code in VBA Editor :
>
> Range("C24:AF54,C60:AF90,C96:AF126").Select
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
> Formula1:="1"
> Selection.FormatConditions(1).Font.ColorIndex = 11
> Selection.FormatConditions(1).Interior.ColorIndex = 11
> Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
> Formula1:="0,5"
> Selection.FormatConditions(2).Font.ColorIndex = 41
> Selection.FormatConditions(2).Interior.ColorIndex = 41
> Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
> Formula1:="-0,5"
> Selection.FormatConditions(3).Font.ColorIndex = 15
> Selection.FormatConditions(3).Interior.ColorIndex = 15
> Sheets("Test2").Select
>
> after the "Selection.FormatConditions(3).Interior.ColorIndex = 15" line, i
> try adding a fourth conditional format in VBA as follows:
>
> Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
> Formula1:="-1"
> Selection.FormatConditions(4).Font.ColorIndex = 20
> Selection.FormatConditions(4).Interior.ColorIndex = 20
>
> However, when i run the macro, an error is returned at the
> " Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
> Formula1:="-1" " line
>
> is it possible to add a fourth (fifth,..) conditional format ?
>
> thanks
> andy
The "FormatConditions Collection Object" in the VBE help states the
following:
Represents the collection of conditional formats for a single range.
The FormatConditions collection can contain up to three conditional
formats. Each format is represented by a FormatCondition object.
So, you can only have 3 conditional formats if you are using the
conditional formatting option via Excel. However, since you are using
VBA you can create a loop and add your logical test with corresponding
color index. This way you can create as as many "conditional formats"
as you want.
I hope this helps.
Matt
|
Similar Threads
