Day 11 — Formatting, With blocks and WorksheetFunction

The calculation is only half the job. A report nobody can read gets sent back.

Formatting a cell

Sub HeaderRow()
    Range("H1").Value = "Total"
    Range("A1:H1").Font.Bold = True
    Range("A1:H1").Interior.Color = RGB(232, 238, 248)

    Debug.Print "H1 bold?", Range("H1").Font.Bold
End Sub

The colours come through on the grid below. RGB(red, green, blue) takes three numbers from 0 to 255.

Property	Sets	Example
`.Font.Bold`	Bold on or off	`= True`
`.Font.Color`	Text colour	`= RGB(155, 28, 28)`
`.Interior.Color`	Fill colour	`= vbYellow`
`.NumberFormat`	How numbers display	`= "#,##0"`

The built-in colour names vbYellow, vbRed, vbGreen, vbBlue, vbWhite, vbBlack. Fine for flagging something while you work. For a report anybody else will see, use RGB and pick something quieter.

NumberFormat, not Format

Day 8 warned that Format returns text. .NumberFormat is the other half of that story: it changes how a cell looks while the value underneath stays a number you can still total.

Sub FormatColumn()
    Dim i As Long

    Range("H1").Value = "Total"

    For i = 2 To 17
        Cells(i, 8).Value = Cells(i, 6).Value * Cells(i, 7).Value
    Next i

    Range("H2:H17").NumberFormat = "#,##0"
    Debug.Print "H2 is still a number:", Cells(2, 8).Value * 2
End Sub

With - stop repeating yourself

When several lines all start with the same object, name it once.

Sub Repetitive()
    Range("A1").Value = "Sales Report"
    Range("A1").Font.Bold = True
    Range("A1").Font.Color = RGB(10, 26, 61)
    Range("A1").Interior.Color = RGB(240, 180, 41)

    Debug.Print Range("A1").Value
End Sub

The same thing, said once:

Sub Tidy()
    With Range("A1")
        .Value = "Sales Report"
        .Font.Bold = True
        .Font.Color = RGB(10, 26, 61)
        .Interior.Color = RGB(240, 180, 41)
        Debug.Print .Value, .Address
    End With
End Sub

The dot is doing the work Inside a With block, a line starting with . means "on the thing I named". Miss the dot and you are talking about something else entirely - usually the active sheet - and it will not always be an error.

Highlighting on a condition

Loop, test, format. This is conditional formatting that you control completely.

Sub FlagBigOrders()
    Dim i As Long
    Dim lastRow As Long
    Dim value As Double

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("H1").Value = "Total"

    For i = 2 To lastRow
        value = Cells(i, 6).Value * Cells(i, 7).Value
        Cells(i, 8).Value = value

        If value >= 100000 Then
            With Cells(i, 8)
                .Interior.Color = RGB(255, 243, 205)
                .Font.Bold = True
            End With
        End If
    Next i

    Range("H2:H17").NumberFormat = "#,##0"
End Sub

Three rows should light up. Change 100000 to 50000 and run it again - that brings it up to eight.

WorksheetFunction - borrowing Excel’s own functions

You do not have to rewrite SUM in a loop. Excel already has it, and VBA can call it.

Sub BorrowedFunctions()
    Debug.Print "Units:", WorksheetFunction.Sum(Range("F2:F17"))
    Debug.Print "Average price:", WorksheetFunction.Average(Range("G2:G17"))
    Debug.Print "Dearest:", WorksheetFunction.Max(Range("G2:G17"))
    Debug.Print "Cheapest:", WorksheetFunction.Min(Range("G2:G17"))
    Debug.Print "Filled cells in C:", WorksheetFunction.CountA(Range("C2:C17"))
    Debug.Print "North orders:", WorksheetFunction.CountIf(Range("C2:C17"), "North")
End Sub

SumIf and VLookup work too, and they save a lot of loop writing:

Sub SummaryWithFunctions()
    Dim i As Long
    Dim regions(1 To 4) As String

    regions(1) = "North": regions(2) = "South"
    regions(3) = "East":  regions(4) = "West"

    For i = 2 To 17
        Cells(i, 8).Value = Cells(i, 6).Value * Cells(i, 7).Value
    Next i

    Cells(1, 10).Value = "Region"
    Cells(1, 11).Value = "Value"

    For i = 1 To 4
        Cells(i + 1, 10).Value = regions(i)
        Cells(i + 1, 11).Value = WorksheetFunction.SumIf( _
            Range("C2:C17"), regions(i), Range("H2:H17"))
        Debug.Print regions(i), Cells(i + 1, 11).Value
    Next i

    Range("J1:K1").Font.Bold = True
    Range("K2:K5").NumberFormat = "#,##0"
End Sub

WorksheetFunction stops the macro when it finds nothing WorksheetFunction.VLookup with no match does not return #N/A - it raises a run-time error and your macro halts. In real Excel you guard it with Application.VLookup and a test, or with On Error. Here it stops with a message telling you what it could not find.

Try these yourself

Make row 1 bold with a grey fill, using a With block.
Fill column H, then colour every cell where units are over 10.
Use WorksheetFunction.SumIf to total the Laptop orders.
Apply the format "#,##0" to H2:H17 and prove the values are still numbers.
Explain the difference between Format(x, "#,##0") and .NumberFormat = "#,##0".

← PreviousDay 10 — Arrays, and why they make macros fast Next →Day 12 — Practice questions and interview answers