Colour Alternate Rows using VBA code

[rounak2]

I wish to colour alternate rows in the worksheet. Sure, there must be some inbuilt feature for doing so which I have been unable to find. But I was trying to do it through VBA. I used the macro recorder to see what code is generated when I color two altenate rows. The code was as follows:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 06.04.2008 by One
'

'

Rows("3:3").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

What I want to know is : Is there someway I could ask excel to increment row numbers by 2....
what i mean is.....

x=2
Rows("1+x:1+x").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

and then I will run a counter to keep it incrementing. THe problem is excel does not even seem to understand this...(leave alone incrementing it)

x=2
Rows("x:x").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With(

Please guide me through this problem. I am a beginner in VBA and hence I would like to solve this problem through VBA coding only.

Thanks in advance.

 

[Andrew Poulsom]

No need for a macro. Select all the rows yo want to coulour, then choose Format|Conditional Formatting from the menu. Condition 1 Formula is:

=MOD(ROW(),2)=0

choose a Format and click OK twice.

 

[VoG]

If you do want a macro

Sub ColorAlternate()
Dim LR As Long, i As Long
'Stop the screen from flickering
Application.ScreenUpdating = False
'Find the last filled row in column A
LR = Range("A" & Rows.Count).End(xlUp).Row
'Loop through the filled rows in steps of 2
For i = 2 To LR Step 2
'Colour alternate rows
    Rows(i).EntireRow.Interior.ColorIndex = 6
Next i
'Turn screen updating on again
Application.ScreenUpdating = True
End Sub

 

[rounak2]

Thanks Andrew. Thanks Vog II.

I am going to REPORT both of you. You are just too fast in replying queries. Thank you so much.

 

[vipulgos]

No need for a macro. Select all the rows yo want to coulour, then choose Format|Conditional Formatting from the menu. Condition 1 Formula is:

=MOD(ROW(),2)=0

choose a Format and click OK twice.

How to do this with some rows are hidden .e.g. i have filtered row, where there is visible rows as under:
Row no.-1, 4, 10, 12,13,16 etc. I want to have different coloured VISIBLE ROWS
please guide me

 

[Andrew Poulsom]

Try:

=MOD(SUBTOTAL(3,$A$1:$A1),2)=0

 

[vipulgos]

sorry not working, it colours only first selected row. And i want to edit my earlier post. I want to colour every alternate visible row with blue colour

 

[Andrew Poulsom]

You need to apply the conditional formatting to all your rows, when A1 is the active cell.

 

[vipulgos]

I have done it but it gives me the result like this:
It applies colours to all the rows which are unfiltered. I have filter the data from row 5 to row 55. The result is that the row no. 1 to 5 and 56 to all below are getting blue in colour

 

[Dougie1]

Why not just fill an entire column with 1s & 2s e.g.

1
2
1
2
1
2

Then filter on the 2s and colour those rows

Cheers,

D