CODEWITHSUNDEEP
vbaexcel
Gia
Gia

Reputation: 13

Looping through range using variables (rows and columns)

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.

Upvotes: 1

Views: 5017

Answers (3)

Ben
Ben

Reputation: 355

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

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

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

Upvotes: 0

Dylan Lau
Dylan Lau

Reputation: 81

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

Upvotes: 1

Related Questions