# 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.