Practical VBA workshop

Goal: sum values by displayed colour while understanding why colour is a weak data field.

A legacy sheet uses red font to indicate unpaid amounts. The macro calculates the total but also shows how to migrate to an explicit Status column.

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 Function SumByFontColour(ByVal values As Range, ByVal sample As Range) As Double
    Dim cell As Range, total As Double
    For Each cell In values.Cells
        If cell.Font.Color = sample.Font.Color And IsNumeric(cell.Value2) Then
            total = total + CDbl(cell.Value2)
        End If
    Next cell
    SumByFontColour = total
End Function

Public Sub RecalculateColourFunctions()
    Application.CalculateFull
End Sub

How the solution works

• Excel does not treat formatting changes as value changes, so a colour-only edit may not recalculate the UDF.
• Font.Color and Interior.Color are different properties; choose the one that matches the workbook’s rule.
• For durable models, store Paid/Unpaid in a status column and use SUMIFS. Colour should communicate the status, not be the only record of it.

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

• Conditional-format colours are not always returned through the basic Font or Interior property; DisplayFormat has restrictions in worksheet UDFs.
• Using a volatile UDF across thousands of cells can slow calculation.
• Colour meanings are inaccessible and can be lost when data is copied to CSV.

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

Add a Status column, reproduce the total with SUMIFS, and compare it with the colour-based result before retiring the legacy rule.

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.