Inconsistent behavior when applying conditional formats

N

N L

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(Type:=xlExpression,
Formula1:="=IF(RC>RC[-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(Type:=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
 
G

Gary Keramidas

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 said:
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(Type:=xlExpression,
Formula1:="=IF(RC>RC[-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(Type:=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
Click to expand...
 
N

N L

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?


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


Greetings,
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
Here is the code in question:
Click to expand...
With ActiveSheet
With .Range(.Cells(3,
3), .Cells(.UsedRange.Rows.Count,
.UsedRange.Columns.Count)).FormatConditions.Add(Type:=xlExpression,
Formula1:="=IF(RC>RC[-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(Type:=xlExpression,
Formula1:="=IF(RC<RC[-1],TRUE,FALSE)")
With .Font
.Color = -16776961 ' red
.TintAndShade = 0
End With
.StopIfTrue = False
End With
End With
Click to expand...
Could it be that I'm hitting a limit on the number of conditional
formats available in a single workbook addressable by VBA?
Click to expand...
Could there be some other part of the program that makes VBA only able
to change the properties of one conditional format?
Click to expand...
Any other ideas would be valued.
Click to expand...
N Lee
Click to expand...
Click to expand...
 
N

N Lee

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?

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.
Click to expand...
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
Click to expand...
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
--
Click to expand...
Gary
Click to expand...

news:5ddc3843-8c69-4fb0-9b95-ebe778efdd56@m73g2000hsh.googlegroups.com...
Click to expand...
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(Type:=xlExpression,
Formula1:="=IF(RC>RC[-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(Type:=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
Click to expand...
Click to expand...
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel VBA 1
VBA adding Conditional Format to Range referencing another column value 3
Conditional Formatting in 2007 1
Conditional Formatting Code 1
Conditional Formatting 5
Conditional Formatting 1
conditional formatting in excel vba 1
conditional format based on column statistics and loop over columns 1

Top