Bug in .FormatConditions.Add Type:=xlExpression, Formula1:=cfExpression

[Thief_]

I have the following dynamic formula:


Quote:
=OFFSET(Summary!$B$1,1,1,COUNTA(Summary!$B$2:$B$65536),COUNTA(Summary!$C$1:$
IV$1))
It will dynamically resize to the size of the data below row 1 and to the
right of columnB. My aim is to shade each second row.

Here is my code to do it in VBA:

Code:
Private Sub FormatSummaryRows()
Dim cfExpression As String

With Worksheets("Summary").Range("DataRange")
.FormatConditions.Delete
cfExpression = "=(ROW(C2)/2=INT(ROW(C2)/2))"
.FormatConditions.Add Type:=xlExpression, Formula1:=cfExpression
.FormatConditions(1).Interior.ColorIndex = 15
End With
End Sub

Here's the problem:

After running my other code, and then running the above procedure, at the
line ".FormatConditions.Add", the range "DataRange" has the address
"$C$2:$AN$32" and the activecell is "$C$2". When the procedure ends, all
seems fine, but when I manually check the Conditional Formatting of, say, C2
to C5, I get the following expressions:
a.. C2: =(ROW(IV65530)/2=INT(ROW(IV65530)/2))

b.. C3: =(ROW(IV65531)/2=INT(ROW(IV65531)/2))

c.. C4: =(ROW(IV65532)/2=INT(ROW(IV65532)/2))

d.. C5: =(ROW(IV65533)/2=INT(ROW(IV65533)/2))
Sometimes it works OK showing $C$2 = "=(ROW(C2)/2=INT(ROW(C2)/2))" and then
sometimes I get the values as in the above list?
When it first happens, it keeps happening each time I run my code then
strangely it just works as it should! Anyone seen this type of (buggy)
behaviour b4?


--
|
+-- Julian
|

[William]

Hi Julian

Using conditional formatting in VB, if you don't want to select the range
you are applying the conditional formatting to, you need to use R1C1
notation. Brief example which you can amend to your circumstances....

Sub test()
With ActiveSheet.Range("A5:A40")
..FormatConditions.Delete
..FormatConditions.Add Type:=xlExpression, _
Formula1:="=ROW(RC)/2=INT(ROW(RC)/2)"
..FormatConditions(1).Interior.ColorIndex = 8
End With
End Sub

-----
XL2002
Regards

William

willwest22@yahoo.com


"Thief_" <thief_@hotmail.com> wrote in message
news:%23ct43cWQFHA.3664@TK2MSFTNGP15.phx.gbl...
>I have the following dynamic formula:
>
>
> Quote:
> =OFFSET(Summary!$B$1,1,1,COUNTA(Summary!$B$2:$B$65536),COUNTA(Summary!$C$1:$
> IV$1))
> It will dynamically resize to the size of the data below row 1 and to the
> right of columnB. My aim is to shade each second row.
>
> Here is my code to do it in VBA:
>
> Code:
> Private Sub FormatSummaryRows()
> Dim cfExpression As String
>
> With Worksheets("Summary").Range("DataRange")
> .FormatConditions.Delete
> cfExpression = "=(ROW(C2)/2=INT(ROW(C2)/2))"
> .FormatConditions.Add Type:=xlExpression, Formula1:=cfExpression
> .FormatConditions(1).Interior.ColorIndex = 15
> End With
> End Sub
>
> Here's the problem:
>
> After running my other code, and then running the above procedure, at the
> line ".FormatConditions.Add", the range "DataRange" has the address
> "$C$2:$AN$32" and the activecell is "$C$2". When the procedure ends, all
> seems fine, but when I manually check the Conditional Formatting of, say,
> C2
> to C5, I get the following expressions:
> a.. C2: =(ROW(IV65530)/2=INT(ROW(IV65530)/2))
>
> b.. C3: =(ROW(IV65531)/2=INT(ROW(IV65531)/2))
>
> c.. C4: =(ROW(IV65532)/2=INT(ROW(IV65532)/2))
>
> d.. C5: =(ROW(IV65533)/2=INT(ROW(IV65533)/2))
> Sometimes it works OK showing $C$2 = "=(ROW(C2)/2=INT(ROW(C2)/2))" and
> then
> sometimes I get the values as in the above list?
> When it first happens, it keeps happening each time I run my code then
> strangely it just works as it should! Anyone seen this type of (buggy)
> behaviour b4?
>
>
> --
> |
> +-- Julian
> |
>
>

[Duke Carey]

You can really simplify your conditional format formula to

=MOD(ROW(),2)=0

thereby obviating any need for cell references in the condition itself



"Thief_" wrote:

> I have the following dynamic formula:
>
>
> Quote:
> =OFFSET(Summary!$B$1,1,1,COUNTA(Summary!$B$2:$B$65536),COUNTA(Summary!$C$1:$
> IV$1))
> It will dynamically resize to the size of the data below row 1 and to the
> right of columnB. My aim is to shade each second row.
>
> Here is my code to do it in VBA:
>
> Code:
> Private Sub FormatSummaryRows()
> Dim cfExpression As String
>
> With Worksheets("Summary").Range("DataRange")
> .FormatConditions.Delete
> cfExpression = "=(ROW(C2)/2=INT(ROW(C2)/2))"
> .FormatConditions.Add Type:=xlExpression, Formula1:=cfExpression
> .FormatConditions(1).Interior.ColorIndex = 15
> End With
> End Sub
>
> Here's the problem:
>
> After running my other code, and then running the above procedure, at the
> line ".FormatConditions.Add", the range "DataRange" has the address
> "$C$2:$AN$32" and the activecell is "$C$2". When the procedure ends, all
> seems fine, but when I manually check the Conditional Formatting of, say, C2
> to C5, I get the following expressions:
> a.. C2: =(ROW(IV65530)/2=INT(ROW(IV65530)/2))
>
> b.. C3: =(ROW(IV65531)/2=INT(ROW(IV65531)/2))
>
> c.. C4: =(ROW(IV65532)/2=INT(ROW(IV65532)/2))
>
> d.. C5: =(ROW(IV65533)/2=INT(ROW(IV65533)/2))
> Sometimes it works OK showing $C$2 = "=(ROW(C2)/2=INT(ROW(C2)/2))" and then
> sometimes I get the values as in the above list?
> When it first happens, it keeps happening each time I run my code then
> strangely it just works as it should! Anyone seen this type of (buggy)
> behaviour b4?
>
>
> --
> |
> +-- Julian
> |
>
>
>

[Thief_]

Thanks William & Duke,

I'm have converted to "=MOD(ROW(),2)=0" now. gr8.
Can anyone explain why I got the massive 65K row numbers in my conditional
formula (as per my original post)?

--
|
+-- Thief_
|

"Duke Carey" <DukeCarey@discussions.microsoft.com> wrote in message
news:40377B3C-8255-4580-B3D8-ACF80F2A03A2@microsoft.com...
> You can really simplify your conditional format formula to
>
> =MOD(ROW(),2)=0
>
> thereby obviating any need for cell references in the condition itself
>
>
>
> "Thief_" wrote:
>
> > I have the following dynamic formula:
> >
> >
> > Quote:
> >
=OFFSET(Summary!$B$1,1,1,COUNTA(Summary!$B$2:$B$65536),COUNTA(Summary!$C$1:$
> > IV$1))
> > It will dynamically resize to the size of the data below row 1 and to
the
> > right of columnB. My aim is to shade each second row.
> >
> > Here is my code to do it in VBA:
> >
> > Code:
> > Private Sub FormatSummaryRows()
> > Dim cfExpression As String
> >
> > With Worksheets("Summary").Range("DataRange")
> > .FormatConditions.Delete
> > cfExpression = "=(ROW(C2)/2=INT(ROW(C2)/2))"
> > .FormatConditions.Add Type:=xlExpression, Formula1:=cfExpression
> > .FormatConditions(1).Interior.ColorIndex = 15
> > End With
> > End Sub
> >
> > Here's the problem:
> >
> > After running my other code, and then running the above procedure, at
the
> > line ".FormatConditions.Add", the range "DataRange" has the address
> > "$C$2:$AN$32" and the activecell is "$C$2". When the procedure ends, all
> > seems fine, but when I manually check the Conditional Formatting of,
say, C2
> > to C5, I get the following expressions:
> > a.. C2: =(ROW(IV65530)/2=INT(ROW(IV65530)/2))
> >
> > b.. C3: =(ROW(IV65531)/2=INT(ROW(IV65531)/2))
> >
> > c.. C4: =(ROW(IV65532)/2=INT(ROW(IV65532)/2))
> >
> > d.. C5: =(ROW(IV65533)/2=INT(ROW(IV65533)/2))
> > Sometimes it works OK showing $C$2 = "=(ROW(C2)/2=INT(ROW(C2)/2))" and
then
> > sometimes I get the values as in the above list?
> > When it first happens, it keeps happening each time I run my code then
> > strangely it just works as it should! Anyone seen this type of (buggy)
> > behaviour b4?
> >
> >
> > --
> > |
> > +-- Julian
> > |
> >
> >
> >