I have the following dynamic formula:


Quote:
=OFFSET(Summary!$B$1,1,1,COUNTA(Summary!$B$2:$B$65 536),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
|