VBA Course: Loops Exercise

To put into practice what has been seen so far, we will step by step create a macro that will number from 1 to 100 a square range of cells 10 by 10 and color every other cell, preview:

Here's the starting point of the exercise:

Sub loopsExercise()
    
    '...
    
End Sub

To start, add a For loop that will number cells from 1 to 10 in the first row, preview:

Take a moment to create this loop before moving on to the solution...

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

The solution:

Sub loopsExercise()
    
    Dim column As Integer
    
    'Column loop
    For column = 1 To 10
        Cells(1, column) = column
    Next
    
End Sub

Now, create a second loop that repeats the first loop for 10 rows, preview:

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

The solution:

Sub loopsExercise()
    
    Dim column As Integer, row As Integer

    'Row loop
    For row = 1 To 10
        
        'Column loop
        For column = 1 To 10
            Cells(row, column) = column
        Next
        
    Next
    
End Sub

Currently, the rows are numbered from 1 to 10.

Now, find a solution to get numbering from 1 to 100, preview:

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

A "simple" solution is to use a variable that increments by 1 after each entry into a cell:

Sub loopsExercise()
    
    Dim column As Integer, row As Integer, value As Integer
    
    'Value of the first cell
    value = 1
    
    'Row loop
    For row = 1 To 10
        
        'Column loop
        For column = 1 To 10
            Cells(row, column) = value
            value = value + 1 'Value incremented by 1
        Next
        
    Next
    
End Sub

Another solution is to calculate the value to be inserted in the cell using the column and row numbers:

Sub loopsExercise()
    
    Dim column As Integer, row As Integer
    
    'Row loop
    For row = 1 To 10
        
        'Column loop
        For column = 1 To 10
            Cells(row, column) = (row - 1) * 10 + column
        Next
        
    Next
    
End Sub

To complete the exercise, there is still the task of coloring every other cell using an If statement and the Mod operator (which returns the remainder of a division), preview:

.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.

The solution:

Sub loopsExercise()
    
    Dim column As Integer, row As Integer
    
    'Row loop
    For row = 1 To 10
        
        'Column loop
        For column = 1 To 10
        
            Cells(row, column) = (row - 1) * 10 + column
            
            'Coloring every other cell
            If (row + column) Mod 2 = 0 Then 'If the remainder of division by 2 equals 0
                Cells(row, column).Interior.Color = RGB(220, 220, 220)
            End If
            
        Next
        
    Next
    
End Sub

The condition (row + column) Mod 2 = 0 is true if the remainder of dividing (row + column) by 2 equals 0 (knowing that the remainder of dividing a positive integer by 2 can only be 0 or 1).

For a simpler example, if the objective of the exercise was to color the even rows (ignoring the columns), the condition would have been row Mod 2 = 0.