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
> |
>
>