Alternate Color

Basic Exercise

Statement

Create a macro that paints table rows by alternating between white (or no fill) and light gray.

Suggested Concepts:

Dim - Declares a variable.
For Next - Repeats a group of statements a specified number of times.
Cells - Allows VBA to interact with cells in the worksheet.

Remember that in most cases there is more than one way to solve the exercise.

Use Step to change the counter increase without using the For Next loop.

Solved Exercise

Exercise Solution

First we declare the variables

Dim UltLinha As Long
Dim UltColuna As Long
Dim i As Integer

Assign the row and column containing data to variables
```
UltLinha = 10
UltColuna = 4
```
We create an For Next loop from the first line with data up to the last one, but interleaving the lines through the use of Step
```
For i = 3 To UltLinha Step 2
    Range(Cells(i, 1), Cells(i, UltColuna)).Interior.Color = RGB(200, 200, 200)
Next i
```

The final code compiled would read as follows:

Dim LastRow As Long
Dim LastColumn As Long
Dim i As Integer

LastRow = Cells(Rows.Count, 2).End(xlUp).Row
LastColumn = Cells(2, Columns.Count).End(xlToLeft).Column

For i = 3 To LastRow Step 2
    Range(Cells(i, 1), Cells(i, LastColumn)).Interior.Color = RGB(200, 200, 200)
Next i

Consolidated Answer

    Sub Solution()

    Dim LastRow As Long
    Dim LastColumn As Long
    Dim i As Integer

    LastRow = 10
    LastColumn = 4

    For i = 3 To LastRow Step 2
        Range(Cells(i, 1), Cells(i, LastColumn)).Interior.Color = RGB(200, 200, 200)
    Next i

    End Sub

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED