Looping through range using variables (rows and columns)

Question

I'm writing a simple formatting macro to alternate the row color for a table in Excel.

I want this macro to be able to format any size table (no matter row/column size).

For example, I want the macro to work when I have a chart with 6 rows 4 columns, or 4 rows 5 columns, or 9 rows 10 columns, etc.

Here's the code I have so far - but I'm getting a runtime error.

  If ActiveSheet Is Nothing = False Then
  Set MyWS = ActiveWorkbook.ActiveSheet

  lastCol = MyWS.UsedRange.Columns.Count + 1
  lastRow = MyWS.UsedRange.Rows.Count + 1

  For Each Cell In Range(lastRow, lastCol) ''change range accordingly
    If Cell.Row Mod 2 = 1 Then
        Cell.Interior.ColorIndex = 15 ''color to preference
    Else
        Cell.Interior.ColorIndex = 14 ''color to preference or remove
    End If
  Next Cell
End If

I've tried multiple versions of the Range - having the column var come first, having an '&' instead of a comma, etc.

If I use just Range("A1:A" & lastRow), it'll work but just for the data in column A. I would need it to span across all columns in the chart.

Dylan Lau · Accepted Answer · 2015-09-16 13:46:44Z

1

If the tables are all starting from cell A1, change your for statement to:

For Each Cell In Range("A1", Cells(lastRow, lastCol)) ''change range accordingly

Though also, the way your for loop works is that it is changing every cell. It can be optimized to color the row up to the last column at once.

If ActiveSheet Is Nothing = False Then
  Set MyWS = ActiveWorkbook.ActiveSheet

  lastCol = MyWS.UsedRange.Columns.Count + 1
  lastRow = MyWS.UsedRange.Rows.Count + 1

  Dim i As Integer
  For i = 1 To lastRow
    If i Mod 2 = 1 Then
        Range("A" & i, Cells(i, lastcol)).Interior.ColorIndex = 15
    Else
        Range("A" & i, Cells(i, lastcol)).Interior.ColorIndex = 14
    End If
  Next i
End If

answered Sep 16, 2015 at 13:46

Dylan Lau

811 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dylan Lau Over a year ago

Oops, also didn't need to iterate through the rows using an integer. Could've iterated using For Each r In MyWs.UsedRange.Rows as @A.S.H answered.

A.S.H Over a year ago

thanks for your note. Funny thing, my answer was voted down, by some unknown who did not have the courage to mention any reason for voting down...

A.S.H · Accepted Answer · 2015-09-16 13:47:54Z

0

Try this:

  Dim r As Range
  For Each r In MyWs.UsedRange.Rows
    If r.Row Mod 2 = 1 Then
        r.Interior.ColorIndex = 15
    Else
        r.Interior.ColorIndex = 14
    End If
  Next r

answered Sep 16, 2015 at 13:47

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Comments

Ben · Accepted Answer · 2015-09-16 13:48:27Z

0

Always good to include Option Explicit in your code modules. Try the following:

    Option Explicit

    Sub test()

    Dim MyWS As Excel.Worksheet
    Dim objRow As Excel.Range
    Dim lastCol As Long
    Dim lastRow As Long
    Dim lngRow As Long

    If ActiveSheet Is Nothing = False Then
      Set MyWS = ActiveWorkbook.ActiveSheet

      lastCol = MyWS.UsedRange.Columns.Count + 1
      lastRow = MyWS.UsedRange.Rows.Count + 1

      For lngRow = 1 To lastRow
            Set objRow = MyWS.Range(MyWS.Cells(lngRow, 1), MyWS.Cells(lngRow, lastCol))
        If lngRow Mod 2 = 1 Then
            objRow.Interior.ColorIndex = 15 'color to preference
        Else
            objRow.Interior.ColorIndex = 14 'color to preference or remove
        End If
      Next lngRow

    End If

    End Sub

answered Sep 16, 2015 at 13:48

Ben

3653 silver badges11 bronze badges

4 Comments

Ben Over a year ago

Agreed with @Dylan below - there is a tacit assumption that every table starts from A1 here. But if so, can just do each row, don't have to do every cell.

A.S.H Over a year ago

iterating through rows is much much simpler

Ben Over a year ago

@A.S.H it is indeed, and I didn't realise Rows property would only address columns in UsedRange, so I just learnt something :) Original post seems to indicate an intention to shade one extra row and one extra column, so perhaps you'd need to extend that range slightly to meet the requirement, if that was intention? Also got downvoted, also don't know why!

A.S.H Over a year ago

seems all the answers were vote down lol. Prob'ly we did not quite understand the question :)

Collectives™ on Stack Overflow

Looping through range using variables (rows and columns)

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related