Believe it or not, I've already tried using "with .formatconditions(1)
and (2)". Even when I do that, both statements make changes on the
first format condition, EVEN IF I CHANGE THE NUMBERS! It's crazy.
Thanks for the suggestion, though. Any other ideas?


On Jul 22, 4:36 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
here's a snippet that may help you, or maybe not. notice the (1) and (2) so it
can distinguish between which condition to apply it to.

With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$d8$D$" & tbl1Lastrow
End With
With rng.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
rng.FormatConditions(1).Interior.ColorIndex = 37

rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
With rng.FormatConditions(2).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(2).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(2).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(2).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
rng.FormatConditions(2).Interior.ColorIndex = 2

--

Gary

"N L" wrote in message

...

Greetings,

I'm getting some inconsistent behavior when applying conditional
formats through VBA to a sheet. The worksheet is one of several in a
workbook, and some of the other worksheets in the workbook have
conditional formatting applied to them as well.

I am trying to apply two conditional formats to the same range of
cells. Basically, it makes numbers red if they're falling, and blue if
they're rising. In my VBA program, the first conditional format is
added, then the font style is applied properly. Then, the second
conditional format is added, but the font style is changed on the
FIRST conditional format. I cannot get VBA to apply the font style to
the second conditional format.

Here's the strange part: if I create a new workbook, and paste in the
same numbers from my problem workbook, the VBA Sub works correctly,
and the font styles are applied to the proper conditional formats.

Here is the code in question:

With ActiveSheet
With .Range(.Cells(3,
3), .Cells(.UsedRange.Rows.Count,
.UsedRange.Columns.Count)).FormatConditions.Add(Ty pe:=xlExpression,
Formula1:="=IF(RCRC[-1],TRUE,FALSE)")
With .Font
.Color = -1003520 ' blue
.TintAndShade = 0
End With
.StopIfTrue = False
End With
With .Range(.Cells(3,
3), .Cells(.UsedRange.Rows.Count,
.UsedRange.Columns.Count)).FormatConditions.Add(Ty pe:=xlExpression,
Formula1:="=IF(RC<RC[-1],TRUE,FALSE)")
With .Font
.Color = -16776961 ' red
.TintAndShade = 0
End With
.StopIfTrue = False
End With
End With

Could it be that I'm hitting a limit on the number of conditional
formats available in a single workbook addressable by VBA?

Could there be some other part of the program that makes VBA only able
to change the properties of one conditional format?

Any other ideas would be valued.

N Lee

On Jul 23, 9:31 am, N L wrote:
Believe it or not, I've already tried using "with .formatconditions(1)
and (2)". Even when I do that, both statements make changes on the
first format condition, EVEN IF I CHANGE THE NUMBERS! It's crazy.
Thanks for the suggestion, though. Any other ideas?

On Jul 22, 4:36 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:

here's a snippet that may help you, or maybe not. notice the (1) and (2) so it
can distinguish between which condition to apply it to.

With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$d8$D$" & tbl1Lastrow
End With
With rng.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
rng.FormatConditions(1).Interior.ColorIndex = 37

rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=0"
With rng.FormatConditions(2).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(2).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(2).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With rng.FormatConditions(2).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
rng.FormatConditions(2).Interior.ColorIndex = 2

--

Gary

"N L" wrote in message

...

Greetings,

I'm getting some inconsistent behavior when applying conditional
formats through VBA to a sheet. The worksheet is one of several in a
workbook, and some of the other worksheets in the workbook have
conditional formatting applied to them as well.

I am trying to apply two conditional formats to the same range of
cells. Basically, it makes numbers red if they're falling, and blue if
they're rising. In my VBA program, the first conditional format is
added, then the font style is applied properly. Then, the second
conditional format is added, but the font style is changed on the
FIRST conditional format. I cannot get VBA to apply the font style to
the second conditional format.

Here's the strange part: if I create a new workbook, and paste in the
same numbers from my problem workbook, the VBA Sub works correctly,
and the font styles are applied to the proper conditional formats.

Here is the code in question:

With ActiveSheet
With .Range(.Cells(3,
3), .Cells(.UsedRange.Rows.Count,
.UsedRange.Columns.Count)).FormatConditions.Add(Ty pe:=xlExpression,
Formula1:="=IF(RCRC[-1],TRUE,FALSE)")
With .Font
.Color = -1003520 ' blue
.TintAndShade = 0
End With
.StopIfTrue = False
End With
With .Range(.Cells(3,
3), .Cells(.UsedRange.Rows.Count,
.UsedRange.Columns.Count)).FormatConditions.Add(Ty pe:=xlExpression,
Formula1:="=IF(RC<RC[-1],TRUE,FALSE)")
With .Font
.Color = -16776961 ' red
.TintAndShade = 0
End With
.StopIfTrue = False
End With
End With

Could it be that I'm hitting a limit on the number of conditional
formats available in a single workbook addressable by VBA?

Could there be some other part of the program that makes VBA only able
to change the properties of one conditional format?

Any other ideas would be valued.

N Lee

Still seeking a solution. Does anyone have any idea why Excel would
confuse these two FormatConditions?