12. Macros and VBA

12.19 Writing a Small Office Script: Colour the Late Orders

By Ravindra Bagale · Last updated: 6 October 2026

Example first. Ruhi wants the script that a recorder cannot make: look at every order, decide if it is late (more than 15 minutes), colour only those rows, and write the count. That needs a loop and an if, so we write it in the Code Editor. It is the Office Scripts twin of the TagLateOrders VBA macro in this chapter's answer key, and it works on the Table named tblOrders.

First look at the data (before → after):

Expected result — After Run (late rows filled light yellow)

About this data: Teaching sample for a fictional shop “Orbit Logistics”. Figures and names (e.g. Vikram Rao) are made up for this lesson on writing a small office script colour the late.

Expected result — Orders!K1 and the Output area

About this data: Demo table only — not real customer or company data. Built to practise writing a small office script colour the late.

const table = workbook.getTable("tblOrders");
const minsCol = table.getColumnByName("Mins").getIndex();
if (mins > 15) { …setColor("#FFE699"); late++; }

The script

function main(workbook: ExcelScript.Workbook): number {
  // 1. Find the table by name (safer than "the active sheet")
  const table = workbook.getTable("tblOrders");
  const body = table.getRangeBetweenHeaderAndTotal();
  const values = body.getValues();

  // 2. Which column holds the minutes?
  const minsCol = table.getColumnByName("Mins").getIndex();

  // 3. Walk the rows; colour late rows and count them
  let late = 0;
  for (let i = 0; i < values.length; i++) {
    const mins = values[i][minsCol] as number;
    if (mins > 15) {
      body.getRow(i).getFormat().getFill().setColor("#FFE699");
      late++;
    }
  }

  // 4. Write the count next to the table and return it (Power Automate can use it)
  workbook.getWorksheet("Orders").getRange("K1").setValue(`Late orders: ${late}`);
  console.log(`Late orders: ${late}`);
  return late;
}

Line by line

About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.

Why read once and loop in memory? Each call that talks to the workbook (getValues, setValue, setColor) takes time. Microsoft's guidance is to read data in one go, work in memory and write back as few times as possible. Here we read once; we write only for the 3 late rows and K1.

Make it safe to run twice. Right now, if BLK-1001 was late yesterday and is fixed today, its yellow fill stays. Add one line before the loop to clear old colours first:

body.getFormat().getFill().clear();

How this was tested: the script was compile-checked with the TypeScript compiler against the ExcelScript method names from Microsoft's reference, and run against a small mock of the 10 orders. It coloured rows AMN-1005, BLK-1007, BLK-1010 and returned 3. Run it in your own Excel on the web to see the real colours.

Microsoft sources: Office Scripts in Excel (overview); Platform limits, requirements and error messages for Office Scripts.

Practice task (try it before you open the answer):

1. Change the script so it colours orders with Amount above 500 instead of late orders, and writes "Big orders: n" in K2.
2. What number should it return on our 10 orders?
3. Add the clear line so old colours are removed first.

Follow the formula or ribbon steps shown in this lesson (keep values numeric).

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.