Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Fill Color
How can I fill the color in every other row starting at row 2 in vba code?
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill color and font color icons do not work in excel | Excel Discussion (Misc queries) | |||
color fill button is not adding color to my spread sheet | Excel Worksheet Functions | |||
My fill color and font color do not work in Excel Std Edition 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 will not display color fonts or color fill cells | Excel Worksheet Functions | |||
My excel 2003 wont let me fill cells with color or color the tabs. | New Users to Excel |