#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 172
Default Fill Color

How can I fill the color in every other row starting at row 2 in vba code?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,624
Default Fill Color

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



In article ,
Secret Squirrel wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default Fill Color

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)



On Fri, 16 Jan 2009 13:21:01 -0800, Secret Squirrel
wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 172
Default Fill Color

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?

"Chip Pearson" wrote:

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)



On Fri, 16 Jan 2009 13:21:01 -0800, Secret Squirrel
wrote:

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
fill color and font color icons do not work in excel huer59 Excel Discussion (Misc queries) 0 November 29th 07 02:06 PM
color fill button is not adding color to my spread sheet mitchnmd Excel Worksheet Functions 1 September 26th 07 04:36 PM
My fill color and font color do not work in Excel Std Edition 2003 chapstick Excel Discussion (Misc queries) 1 September 11th 05 08:48 PM
Excel 2003 will not display color fonts or color fill cells DaveC Excel Worksheet Functions 1 April 11th 05 04:38 PM
My excel 2003 wont let me fill cells with color or color the tabs. trizog New Users to Excel 2 February 22nd 05 07:43 PM


All times are GMT +1. The time now is 03:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"