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