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 PDF files of this course