Practical VBA workshop

Goal: apply fills, borders and status colours from values while keeping the workbook maintainable.

A task sheet needs clear visual status cues: overdue items, completed items and work still in progress.

What you will build

• A complete procedure that works without Select or Activate.
• Test data and expected behaviour you can verify before using real files.
• Error cases and production checks that prevent silent damage.

Complete VBA example

Option Explicit
Public Sub FormatReportPart2()
    Dim ws As Worksheet, lastRow As Long, cell As Range
    Set ws = ThisWorkbook.Worksheets("Tasks")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    With ws.Range("A1:D" & lastRow)
        .Borders.LineStyle = xlContinuous
        .Borders.Color = RGB(220, 220, 220)
        .VerticalAlignment = xlCenter
    End With
    For Each cell In ws.Range("D2:D" & lastRow)
        Select Case LCase$(Trim$(cell.Value2))
            Case "complete": cell.Interior.Color = RGB(198, 239, 206)
            Case "overdue": cell.Interior.Color = RGB(255, 199, 206)
            Case Else: cell.Interior.Color = RGB(255, 235, 156)
        End Select
    Next cell
End Sub

How the solution works

• RGB values are clearer and more portable than workbook-dependent ColorIndex numbers.
• Normalize text with Trim and LCase before comparing it, otherwise spaces and capitalization create false mismatches.
• For rules that must update instantly when values change, native Conditional Formatting is usually better than a one-time macro.

Run it step by step

1. Download and unzip the practice pack below. Open the matching CSV or copy it into a new macro-enabled workbook.
2. Press Alt + F11, choose File → Import File, and import this chapter’s .bas module.
3. Rename the sample worksheet to the name used in the code, or change the worksheet name in one clearly marked line.
4. Run the procedure with F5. Compare the result with the expected values and boundary cases in the CSV.
5. Undo is not available after a macro changes a workbook. Test on a copy, save only after checking totals, and keep the original source file.

Common mistakes

• Colouring entire rows in a loop is slow; restrict the target to the columns people actually read.
• Font colour alone is not accessible; combine colour with a written status.
• A macro does not automatically rerun after edits unless an event procedure calls it.

Make it production-ready

Add Option Explicit, qualify every workbook and worksheet, validate the last row and input types, and use a single cleanup section to restore ScreenUpdating, EnableEvents and calculation settings after an error. Avoid hard-coded regulatory rates or colour-only business rules. Record the source, effective date and assumptions in a visible Notes sheet.

Practice challenge

Extend the macro so the complete row receives a light green fill and an overdue date in column C receives bold red text.

Test normal data, blanks, text in numeric columns, duplicate values and the exact threshold boundaries. A useful macro should fail clearly rather than produce a plausible but wrong answer.