Range of even/odd rows
 

Hi everyone,

I am using VBA to format a big range of data whose even and odd rows are
formatted differently. Currently I use this code:

(myRange has 3000 rows and 100 columns)
With myRange
.Interior.ColorIndex = 2
For i = 2 To .Rows.count Step 2
.Rows(i).Interior.ColorIndex = 3
Next i
End With

The For loop seems to slow down my code. Is there any way to create a
range of even/odd rows. I tried the following way but I didn't work.

worksheets(1).range("A2:CV2,A4:CV4,....
...,A3000:CV3000").Interior.ColorIndex = 3

Your help or suggestion is very appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Range of even/odd rows
 

use cinditional formatting
select the sheet or table, then Format/conditional formatting, formula is:=
=MOD(ROW(),2)=1


In Code
Sub SetConFormat()
With Range("MyTable")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=MOD(ROW(),2)=1"
.FormatConditions(1).Interior.ColorIndex = 3
End With
End Sub

TIP: if you can do it using menus etc on th esheet directly, then do it
using the macro recorder & examine the resulting code.

Patrick Molloy
Microsoft Excel MVP

"Cool Sport" wrote:

Hi everyone,

I am using VBA to format a big range of data whose even and odd rows are
formatted differently. Currently I use this code:

(myRange has 3000 rows and 100 columns)
With myRange
.Interior.ColorIndex = 2
For i = 2 To .Rows.count Step 2
.Rows(i).Interior.ColorIndex = 3
Next i
End With

The For loop seems to slow down my code. Is there any way to create a
range of even/odd rows. I tried the following way but I didn't work.

worksheets(1).range("A2:CV2,A4:CV4,....
...,A3000:CV3000").Interior.ColorIndex = 3

Your help or suggestion is very appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!