Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 37 ยท Working with Excel

Excel VBA: Formulas and Formatting

Write formulas from VBA, use WorksheetFunction, apply number formats, fonts, borders, colors and conditional formatting, and build a tidy report header.

A macro can do more than move values around. It can write live formulas, call Excel's own functions from VBA code, and apply the formatting that makes a report readable. This lesson covers all three, and explains the choice you often face: should the calculation live in a cell formula, or in VBA?

VBA is read-only here. Runnable stand-ins are written in JavaScript and model the logic (relative formulas, number formatting) so you can try the ideas in your browser.

Writing formulas

Assign a string that starts with = to the Formula property.

VB
With ThisWorkbook.Worksheets("Data")
    .Range("D2").Formula = "=B2*C2"                 ' one cell
    .Range("D2:D20").Formula = "=B2*C2"             ' fills the block, relative refs adjust
    .Range("D21").Formula = "=SUM(D2:D20)"
    .Range("E2").FormulaR1C1 = "=RC[-2]*RC[-1]"     ' same idea in R1C1 style
End With

When you assign one formula to a multi-cell range, Excel adjusts the relative references for each row, exactly as if you had filled down. Use Formula for English function names; FormulaLocal uses the user's language and is best avoided. Dynamic-array functions such as FILTER or SORT need Formula2, otherwise Excel adds implicit-intersection @ signs.

To turn formulas into plain values (a common finishing step):

VB
With ws.Range("D2:D20")
    .Value2 = .Value2
End With

WorksheetFunction: Excel functions inside VBA

VB
Dim total As Double, biggest As Double, rowNo As Variant
With ThisWorkbook.Worksheets("Data")
    total = Application.WorksheetFunction.Sum(.Range("D2:D20"))
    biggest = Application.WorksheetFunction.Max(.Range("D2:D20"))
    rowNo = Application.Match("Book", .Range("A2:A20"), 0)
End With

If IsError(rowNo) Then
    Debug.Print "Not found"
Else
    Debug.Print "Row offset: " & rowNo
End If

There is a subtle difference: Application.WorksheetFunction.Match raises a run-time error when nothing matches, while Application.Match returns an error value you test with IsError. The second form is usually friendlier. Note too that VBA has its own Round, Format, Len and so on; those are not the worksheet functions.

Formula in a cell or calculation in VBA?

Put it in a cell formula when Do it in VBA when
Users should see it update live It is a one-off or a snapshot
The logic is simple arithmetic or lookup The logic needs loops, branches or other files
You want auditability The result is large and a formula would be slow

Number formats, fonts and colors

VB
With ws.Range("B2:B20")
    .NumberFormat = "#,##0.00"          ' 1,234.50
End With
ws.Range("C2:C20").NumberFormat = "0.0%"
ws.Range("A2:A20").NumberFormat = "yyyy-mm-dd"

With ws.Range("A1:D1")
    .Font.Bold = True
    .Font.Color = RGB(255, 255, 255)
    .Interior.Color = RGB(31, 78, 121)
    .HorizontalAlignment = xlCenter
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
End With
ws.Columns("A:D").AutoFit

Colors are Long values. RGB(r, g, b) builds one; named constants such as vbRed also work. Set NumberFormat before writing text that looks like a number, for example "@" for ZIP codes with a leading zero.

Conditional formatting

VB
Dim rng As Range: Set rng = ws.Range("D2:D20")
rng.FormatConditions.Delete

With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=100")
    .Font.Color = vbRed
    .Interior.Color = RGB(255, 235, 235)
End With

rng.FormatConditions.AddColorScale ColorScaleType:=3

Delete old rules first, otherwise each run of the macro piles up another copy. For rule-based colors you can also color cells directly in a loop, but conditional formatting keeps working when the data changes.

Try it: R1C1 and a number-format model

Relative R1C1 formulas are the same for every row, which is why Excel can fill them down. The first example shows that idea; the second formats numbers like #,##0.00.

JS Fill a relative formula down a column

Press Run (or Ctrl + Enter) to see the console output here.

No output.

JS Number format #,##0.00 and 0.0%

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Common mistakes

  • Assigning text that looks like a formula, such as "=A1+", which raises error 1004.
  • Using WorksheetFunction.VLookup without handling the not-found error.
  • Looping cells to apply the same format when a single Range(...).Font.Bold = True does it all at once.
  • Forgetting to delete conditional formats before re-adding them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens when you assign Formula = "=B2*C2" to Range("D2:D20")?

    Excel treats the formula as written for the top-left cell and adjusts relative references for the others.
  2. 2. How does Application.Match differ from WorksheetFunction.Match?

    Application.Match yields an error value you can test with IsError, so no error handler is needed.
  3. 3. Which property changes only how a number looks, not its stored value?

    NumberFormat controls display. The stored value stays the same.
  4. 4. Why call FormatConditions.Delete before adding rules?

    Each run would otherwise add another identical rule.

Summary

  • Assign formulas with Formula (or Formula2 for dynamic arrays); one assignment fills a block with adjusted references.
  • Use Application.WorksheetFunction or Application.Match to call Excel functions from VBA.
  • Format with NumberFormat, Font, Interior, Borders and conditional formats.
  • Convert formulas to values with rng.Value2 = rng.Value2 when you need a snapshot.

Next: Excel VBA: Data, Tables and Charts