[Excel] Writing a macro to set alternate background colors in rows?

[timeshifter]

I'm a complete and utter Excel n00b, so pardon my ignorance... I've written a few macros for Visual Studio, so I kinda get the idea. Essentially, I want to do exactly what the title says: select a whole bunch of cells, and run a macro that puts a light gray background color to every other row. Not knowing anything about how Excel works, I'm honestly not even sure where to start...

[Akos_beres]

Which version of excel ... approximately how many rows do you need to format? Do you want to format the entire row or maybe rows within a range.

[Siddharth Rout]

Hi Time shifter... It is very easy to record a macro in excel... I am assuming that you are using excel 2003

Open Excel, Click on Menu->Tool->Macros->Record New macro to start the macro and simply perform the task that you want to do...

Once done stop the macro and press Alt+F11 to amend the macro in the module...

If you have any questions do ask...

[Hack]

Actually, Sid, I already did that.

I didn't post it yet, because I am having a problem. This is what the macro gave me.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/25/2009 by Hack
'

'
    Rows("1:1").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    Rows("3:3").Select
     With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    Rows("5:5").Select
     With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
End Sub

Now, what I'm trying to do, and failing it, is replacing Rows("3:3") with a variable like i

For i = 1 To 264
    Sheets("sheet1").Rows("i:i").Select
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
Next

Everything I've tried to replace the hard code numbers has come up with an error. I've tried a wide variety of things, not just the ("i:i") thing.

[Siddharth Rout]

Sub test()
    For i = 1 To 264
        Sheets("sheet1").Rows(i).Select
        With Selection.Interior
            .ColorIndex = 15
            .Pattern = xlSolid
        End With
    Next
End Sub

The i is treated as a variable once you put it in quotes...

[Hack]

I almost said "Thank you sir" except this isn't my thread or my question.

However, I'm sure that when TimeShifter comes along, he will say so himself.

[Siddharth Rout]

@Time Shifter: You can use "Step" in the loop to color alternate rows...

something like

Sub ColorRows()
    '~~> This will color alternate rows starting
    '~~> from row 1 and up till Row 50
    For i = 1 To 50 Step 2
        Sheets("sheet1").Rows(i).Select
        With Selection.Interior
            .ColorIndex = 15
            .Pattern = xlSolid
        End With
    Next
End Sub

[timeshifter]

Sorry for my delayed response... had to run out of the office for a bit, then enjoy my chicken & sausage gumbo

@Akos_beres: Excel 2007, any feasible number (we'll likely be dealing with reports a lot, so possibly thousands of rows at a time), and whatever the user has selected.

@Hack: You have my permission to say thank you

@sid: The goal is to let the user select what they need formatted and run with that... Your code certainly looks simple enough, which I like, but what about the dynamic side of it? How would it handle only formatting alternating rows in a selection?

[Siddharth Rout]

@sid: The goal is to let the user select what they need formatted and run with that... Your code certainly looks simple enough, which I like, but what about the dynamic side of it? How would it handle only formatting alternating rows in a selection?

Gimme a quick moment and I will give the code for that as well

Edit:

Sub ColorRows()
    Dim i As Long, StartRow As Long, EndRow As Long
    
    '~~> Ask the user for the start Row
    StartRow = InputBox("Please enter the starting row:")
    '~~> Ask the user for the last Row
    EndRow = InputBox("Please enter the Last row:")
    
    '~~> This will color alternate rows starting
    '~~> from rStartRow and up till EndRow
    For i = StartRow To EndRow Step 2
        Sheets("sheet1").Rows(i).Select
        With Selection.Interior
            .ColorIndex = 15
            .Pattern = xlSolid
        End With
    Next
End Sub

[timeshifter]

That still requires the user to know what rows they want to highlight... Yes, I get the idea, but my goal was to have it be based on whatever cells are currently highlighted. VS macros have this:

Dim selection As EnvDTE.TextSelection = ActiveDocument.Selection
Dim text As String = selection.Text

to retrieve the selection. How would one do the Excel equivalent?

[Siddharth Rout]

That still requires the user to know what rows they want to highlight... Yes, I get the idea, but my goal was to have it be based on whatever cells are currently highlighted. VS macros have this:

Dim selection As EnvDTE.TextSelection = ActiveDocument.Selection
Dim text As String = selection.Text

to retrieve the selection. How would one do the Excel equivalent?

Ok how about this...

the user will make a selection and then run this macro.... It will color alternate rows in the selection made by the user...

Sub ColorRows()
    Dim pos1 As Long, pos2 As Long
    Dim Addr As String
    Dim StartRow As Long, EndRow As Long
    
    '~~> Get the adress of the selected range
    '~~> will be of the format $1:$10
    Addr = Selection.Address
    
    '~~> Locate the first "$"
    pos1 = InStr(1, Addr, "$")
    
    '~~> Locate the ":"
    pos2 = InStr(1, Addr, ":")
    
    '~~> Get Startrow
    StartRow = Mid(Addr, pos1 + 1, pos2 - pos1 - 1)
    
    '~~> Get Endrow
    EndRow = Mid(Addr, pos2 + 2, Len(Addr) - pos2)
    
    '~~> This will color alternate rows starting
    '~~> from StartRow and up till EndRow
    For i = StartRow To EndRow Step 2
        Sheets("sheet1").Rows(i).Select
        With Selection.Interior
            .ColorIndex = 15
            .Pattern = xlSolid
        End With
    Next
End Sub

Hope this is what you wanted?

[timeshifter]

First try kinda threw an error... I'll go in and try to debug it later. Getting the selection is the tricky part... from there, it should just be brushing up on VBA a little bit... which I'm moderately familiar with, from writing macros for VS. Thanks for the start, though

[Siddharth Rout]

What error did you get?

[anhn]

Another way is to use Conditional formatting, that can be set manually or by code:

   Selection.FormatConditions.Delete
   Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
   Selection.FormatConditions(1).Interior.ColorIndex = 15

[timeshifter]

I tried something that claimed to be conditional formatting, with little success... maybe I'm just too much of an Excel n00b to know how to properly use the tools it has...

[Siddharth Rout]

something to get you started...

http://www.homeandlearn.co.uk/excel2...l2007s6p2.html

[anhn]

Do you think this post should be in CodeBank?

1. Method 1: Hard-color alternate rows
This code will need to be re-run if later a row will be inserted or deleted within the range.

Sub AlternateRowColors(Optional aRange As Range, _
                       Optional BG0 As Long = 19, _
                       Optional BG1 As Long = 20)
   Dim BG As Variant, Area As Range, r As Long

   BG = Array(BG0, BG1) ' use 2 colorindex of your choices
   If aRange Is Nothing Then Set aRange = Selection
   '-- we need to use Areas because aRange or Selection may consist of
   '-- more than one areas, otherwise only the first area will be colored
   For Each Area In aRange.Areas
      For r = 1 To Area.Rows.Count
         Area.Rows(r).Interior.ColorIndex = BG(r Mod 2)
      Next
   Next
End Sub

Sub UsageExamples()
'-- Clear all interior color
   [A1:K20].Interior.ColorIndex = xlNone
   
'-- Example 1:
   [A4:C10].Select
   AlternateRowColors
   MsgBox "[A4:C10] was selected and colored as default"
   
'-- Example 2:
   [B6:E12].Select
   AlternateRowColors , 15, xlNone
   MsgBox "[B6:E12] was selected and colored with my choices"

'-- Example 3:
   AlternateRowColors [H1:K20]
   MsgBox "[H1:K20] was colored as default without selected"

'-- Example 4:
   AlternateRowColors [A1:D10,D8:F15], 18, 23
   MsgBox "2 Areas [A1:D10] and [D8:F15] was colored with my choices"
   
   Range("A1").Select
   
End Sub

2. Method 2: Use Conditional Formating to color alternate rows
The advantage of Conditional formatting method here is when you insert a new row or delete a row within the range then you don't need to re-run the code, the colors will be auto adjusted.

Sub ConditionalAltRowColors()
   '-- No need to use areas here even the Selection consist of more than one areas.
   '-- You can replace Selection with any range of your choices.
   With Selection.FormatConditions
      .Delete
      .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0"
      .Item(1).Interior.ColorIndex = 19
      .Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
      .Item(2).Interior.ColorIndex = 20
   End With
End Sub

PS.
No need to say thanks to my code if it works for you, but if it is used then it should be credited.
Rows(i) instead of Rows(i & ":" & i) was what I taught someone when he was still young.