Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Bug in .FormatConditions.Add Type:=xlExpression, Formula1:=cfExpression
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 | |
#3
|
|||
|
|||
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$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 | |
#4
|
|||
|
|||
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" wrote in message ... 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$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 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert date in macro | New Users to Excel |