Range of even/odd rows

  • Thread starter Thread starter Cool Sport
  • Start date Start date
C

Cool Sport

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top