Formatting Every 3rd Row the same

[Chipper]

I am new to this BB and have found the content very helpful in learning how to create macros. Thanks to all for sharing your knowledge.

Now for my problem: I have a macro that copies data fields from multiple workbooks and multiple worksheets into one output worksheet. On the output worksheet, I am trying to format every 3rd row the same (i.e. fill color is Yellow). The number of rows in the output file is variable in length and changes regularly. Can someone help me with the code for the Macro to format every 3rd row the same through the last row of data in the output sheet and not beyond?

Thanks.

[NateO]

The following will add a conditional format to your worksheet (yellow), I like the conditional format as it's dynamic, if you delete a row, it will automatically offset. This starts on row 3 and goes every third:

Sub colorin()
Dim i As Long
i = [a65536].End(xlUp).Row
    Sheet1.Range("a1:b" & i).FormatConditions.Add Type:=xlExpression, _
    Formula1:="=MOD(ROW(),3)=0"  ' first sheet
    Sheet1.Range("a1:b" & i).FormatConditions(1).Interior.ColorIndex = 36
End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-17 14:41 ]

[NateO]

Or if you want the first row, then offset three try:

Sub colorin2()
Dim i As Long
i = [a65536].End(xlUp).Row
    Sheet2.Range("a1:b" & i).FormatConditions.Add _
        Type:=xlExpression, _
    Formula1:="=OR((MOD((ROW()-1),3)=0),(ROW()=1))"  ' first sheet
    Sheet2.Range("a1:b" & i).FormatConditions(1).Interior.ColorIndex = 36
End Sub

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-17 14:42 ]

[Asala42]

G'day all,

NatO beat me to it but here's another bit (my beginner approach):


Sub Format3rdrow()

Dim Rownum As Integer

Sheets("sheet1").Activate
Rownum = 3

Do While Rownum < 20
Rows(Rownum).Select


With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Rownum = Rownum + 3
Loop

End Sub


Adam

[Jack in the UK]

the loop method could prove slow but none less efective i add...

[Brian]

You can do also this without a macro: Format > Conditional formatting > formula. (Though a macro is obviously appropriate in your case because the worsksheet is generated by a macro.)

Regards.

Brian

[Chipper]

Thanks for the responses.

I have not had a chance to try them out yet and I did not want to wait to say thanks to those who responded.