4 conditional formats

[andy]

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

 

[meh2030]

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

 

[andy]

thanks!
i used a select case statement to add a fourth conditional format and it
works.