VBA Course: Loops (exercise)

To practice what we have just learned, we'll go through the step-by-step process of creating a macro to add background colors to a 10x10 checkerboard of cells (in red and black) starting from the currently selected cell. See below :

seebelow - loops continued

Here's the first step of the exercise :

Sub loops_exercise()

    Const NB_CELLS As Integer = 10 'Number of cells to which we want to add background colors

    '...
   
End Sub

Let's start out by adding a For loop to add black backgrounds to the cells in column A (The NB_CELLS constant being 10). See below:

1 - loops continued

Take a moment to create this loop on your own before you look at the solution ...

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

The solution :

Sub loops_exercise()

    Const NB_CELLS As Integer = 10 'Number of cells to which we want to add background colors

    For r = 1 To NB_CELLS 'r => row number
   
        Cells(r, 1).Interior.Color = RGB(0, 0, 0) 'Black

    Next
   
End Sub

The next step is making every other cell's background red with an If instruction (based on whether the row numbers are even or odd). See below :

2 - loops continued

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

The solution :

Sub loops_exercise()

    Const NB_CELLS As Integer = 10 'Number of cells to which we want to add background colors

    For r = 1 To NB_CELLS 'r => row number
   
       If r Mod 2 = 0 Then 'Mod => is the remainder from division
           Cells(r, 1).Interior.Color = RGB(200, 0, 0) 'Red
       Else
           Cells(r, 1).Interior.Color = RGB(0, 0, 0) 'Black
       End If

    Next
   
End Sub

The condition If r Mod 2 = 0 means : if the remained when we divide r by 2 equals 0 ...

Only row numbers that are even will have a remainder of 0 when they are divided by 2.

Now create a loop that executes the loop we already have for the 10 columns. See below :

3 - loops continued

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

The solution :

Sub loops_exercise()

    Const NB_CELLS As Integer = 10 '10x10 checkerboard of cells

    For r = 1 To NB_CELLS 'r => row number
   
        For c = 1 To NB_CELLS 'c => column number
       
           If r Mod 2 = 0 Then
               Cells(r, c).Interior.Color = RGB(200, 0, 0) 'Red
           Else
               Cells(r, c).Interior.Color = RGB(0, 0, 0) 'Black
           End If
           
        Next
    Next
   
End Sub

Now the second loop is nested within the first one.

To achieve this result ...

4 - loops continued

Replace :

If r Mod 2 = 0 Then

With :

If (r + c) Mod 2 = 0 Then

All that's left to do is to edit the code so that the checkerboard is created starting from the currently selected cell (rather than A1). See below :

5 - loops continued

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

The solution :

Sub loops_exercise()

   Const NB_CELLS As Integer = 10 '10x10 checkerboard of cells
   Dim offset_row As Integer, offset_col As Integer ' => adding 2 variables
   
   'Shift (rows) starting from the first cell = the row number of the active cell - 1
   offset_row = ActiveCell.Row - 1
   'Shift (columns) starting from the first cell = the column number of the active cell - 1
   offset_col = ActiveCell.Column - 1
   
   For r = 1 To NB_CELLS 'Row number
   
        For c = 1 To NB_CELLS 'Column number
       
            If (r + c) Mod 2 = 0 Then
            'Cells(row number + number of rows to shift, column number + number of columns to shift)
                Cells(r + offset_row, c + offset_col).Interior.Color = RGB(200, 0, 0) 'Red
            Else
                Cells(r + offset_row, c + offset_col).Interior.Color = RGB(0, 0, 0) 'Black
            End If
           
        Next
   Next
   
End Sub
Download VBA course archive (PDF)