Question

Create a 6 by 6 checkered pattern without using a loop. Also use the wait and offset properties. Create every checkered spot with a 1 second wait time. VBA Excel

Answer 1

Sub loops_exercise()

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

  '...

End Sub

STEP1:

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

Sub loops_exercise()

  Const NB_CELLS As Integer = 6 '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

STEP2:

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 :

Sub loops_exercise()

  Const NB_CELLS As Integer = 6 '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.

STEP3:

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

Sub loops_exercise()

  Const NB_CELLS As Integer = 6 '6x6 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

(Notice the second loop is nested within the first one)

STEP4:

To achieve 6X6 chess board

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 :

Sub loops_exercise()

Const NB_CELLS As Integer = 6 '6x6 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