Fill Color

[Secret Squirrel]

How can I fill the color in every other row starting at row 2 in vba code?

 

[JE McGimpsey]

One way:

Dim n As Long
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = xlColorIndexNone
For n = 2 To 1000 Step 2 'adjust to suit
Cells(n, 1).EntireRow.Interior.ColorIndex = 3
Next n


But you may be interested in having XL maintain that banding
automatically via Conditional Formatting:

http://cpearson.com/excel/banding.htm

 

[Chip Pearson]

Try something like the following:

Sub AAA()
Dim N As Long
Dim LastRow As Long
Dim WS As Worksheet
Set WS = ActiveSheet
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For N = 2 To LastRow Step 2
.Rows(N).Interior.ColorIndex = 3 ' 3 = red
Next N
End With
End Sub

This will fill the rows from row 2 down to the last used row (that has
a non-blank value in column "A") with a red background.

See VBA help for ColorIndex for a list of ColorIndex values and their
corresponding values.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Secret Squirrel]

Hi Chip,

Follow up question....The code you gave me works fine but how can I run it
after I filter my form? Once I filter it then the shading is not set to every
other row due to the filtering. Is there a way to have it automatically shade
every other cell after I filter my data?