VBA-Lesson 8.2. Loops

To practice what we've just learned, we'll walk through the process of creating a macro to paint a 10x10 checkerboard (red and black) starting from the currently active cell. Let's take a look:

Next is the first step of the exercise:

Sub loops_exercise()

     Const NB_CELLS As Integer = 10 'The number of cells we want to paint

     '...
   
End Sub

Let's start by adding a FOR loop to black out column A (Constant NB_CELLS equals 10). See lower:

Try to make this cycle yourself.

Now you can check with the following code:

Sub loops_exercise()

     Const NB_CELLS As Integer = 10 'The number of cells we want to paint

     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 colors each subsequent cell red, using an IF statement (based on whether the row number is even or odd). See lower:

The code for solving this subtask is as follows:

Sub loops_exercise()

     Const NB_CELLS As Integer = 10 'The number of cells we want to paint

     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 remainder of dividing r by 2 is 0...

Only the row number that is even has a remainder of 0 when divided by 2:

How to make another loop that would execute the loop we have already written for 10 columns. See lower:

The code for solving this subtask is as follows:

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 cycle is inserted into the first.

To achieve the following result...

Replace:

If r Mod 2 = 0 Then

At:

If (r + c) Mod 2 = 0 Then

All that remains to be done is to change the code so that the checkerboard is created starting from the selected cell (not A1). See below:

Sub loops_exercise()

    Const NB_CELLS As Integer = 10 '10x10 checkerboard of cells
    Dim offset_row As Integer, offset_col As Integer ' => add 2 variables
   
    'Offset (rows) starting from the first cell = row number of the current active cell - 1
    offset_row = ActiveCell.Row - 1
    'Offset (columns) starting from the first cell = column number of the current 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
             'Cell(row number + additional row offset, column number +
             'additional column offset)
                 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

Articles on the topic: