Python Doc & Data Automation

Highlight Rows with Conditional Formatting in Python

The goal is simple: in an accounts-receivable sheet, colour the entire row amber when an invoice is overdue and red when it is more than 60 days overdue. The first attempt colours only the date cell. The second, applied to A2:H500, turns every row amber — or none of them — and when both colours are added, rows that should be red stay amber.

ws.conditional_formatting.add("A2:H500", FormulaRule(formula=["$F$2<TODAY()"], fill=AMBER))
# every row amber, because every row tests row 2

Root Cause

A conditional formatting rule has one formula for the entire range, written from the perspective of the range's top-left cell. Excel evaluates the rule for each cell by shifting every relative reference by that cell's offset from the top-left corner. To make a whole row follow one column, the column part of the reference must be absolute and the row part relative: $F2. Written as $F$2, every cell tests row 2 — all rows share one result. Written as F2, cells in column B test column G, cells in column C test column H, and results scatter across the row. A formula written for row 1 on a range that starts at row 2 shifts everything by one row, so each row is coloured by its neighbour. Finally, when several rules match the same cell, Excel applies them in priority order; if the amber rule has the higher priority and stops evaluation, the red rule never gets a chance.

Minimal Diagnostic

Evaluate what each rule would do per row, in Python, by expanding its formula the same way Excel does. It exposes absolute-row and off-by-one mistakes without opening Excel.

# pip install "openpyxl>=3.1"
import re
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.utils import range_boundaries

SOURCE = Path("out/receivables.xlsx")
REF = re.compile(r"(\$?)([A-Z]{1,3})(\$?)(\d+)")

def shifted(formula: str, row_offset: int) -> str:
    """Shift relative row numbers in A1 references, as Excel does per row."""
    def repl(m):
        col_abs, col, row_abs, row = m.groups()
        new_row = int(row) if row_abs else int(row) + row_offset
        return f"{col_abs}{col}{row_abs}{new_row}"
    return REF.sub(repl, formula)

def explain_rules(path: Path, sheet: str, sample_rows: int = 3) -> None:
    wb = load_workbook(path)
    try:
        ws = wb[sheet]
        for cf in ws.conditional_formatting:
            min_col, min_row, max_col, max_row = range_boundaries(str(cf.sqref).split()[0])
            for rule in cf.rules:
                if rule.type != "expression":
                    continue
                print(f"{cf.sqref} priority={rule.priority} stopIfTrue={rule.stopIfTrue}")
                for offset in range(sample_rows):
                    print(f"   row {min_row + offset}: {shifted(rule.formula[0], offset)}")
    finally:
        wb.close()

if __name__ == "__main__":
    explain_rules(SOURCE, "Receivables")

A2:H500 priority=1 stopIfTrue=True
   row 2: $F$2<TODAY()
   row 3: $F$2<TODAY()
   row 4: $F$2<TODAY()

Every row evaluates $F$2. The fix is the reference style, not the fill.

The formula is written for the top-left cell The rule range spans columns A to H from row 2 down. The anchor is the top-left cell A2, and the formula is written as if evaluated there, testing dollar F 2. For row 3 Excel reads dollar F 3, for row 4 dollar F 4. The column F is pinned with a dollar sign so every cell in a row tests the same due date. Receivables sheet, rule on A2:H500 1 1 Header row 1 outside the rule range 2 2 Anchor A2 formula written for this cell 3 3 Column F due date pinned with $F 4 4 Row 3 reads $F3 row shifts by one per row

Fix: Absolute Column, Relative Row, Anchored at the First Data Row

Write the formula for the first row of the range, pin the column, leave the row relative, and order the rules so the stronger condition wins. Changed lines are commented.

# pip install "openpyxl>=3.1" "pandas>=2.2"
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Font, PatternFill

DEST = Path("out/receivables.xlsx")
RED = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
AMBER = PatternFill(start_color="FFEB9C", end_color="FFEB9C", fill_type="solid")

invoices = pd.DataFrame({
    "invoice": ["INV-501", "INV-502", "INV-503", "INV-504"],
    "customer": ["Northwind", "Contoso", "Fabrikam", "Adatum"],
    "issued": pd.to_datetime(["2026-06-01", "2026-08-20", "2026-07-05", "2026-09-01"]),
    "amount": [1200.0, 560.0, 8400.0, 99.0],
    "paid": [0.0, 560.0, 0.0, 0.0],
    "due": pd.to_datetime(["2026-07-01", "2026-09-19", "2026-08-04", "2026-10-01"]),
})

def build(frame: pd.DataFrame, dest: Path, headroom: int = 300) -> None:
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="openpyxl") as writer:
        frame.to_excel(writer, sheet_name="Receivables", index=False)
    first, last = 2, len(frame) + 1 + headroom                        # changed: start at data row 2
    rng = f"A{first}:F{last}"
    open_and_due = f'AND($A{first}<>"",$E{first}<$D{first})'          # unpaid and a real row
    wb = load_workbook(dest)
    try:
        ws = wb["Receivables"]
        ws.conditional_formatting.add(rng, FormulaRule(                # changed: red rule added FIRST
            formula=[f"AND({open_and_due},TODAY()-$F{first}>60)"],     # changed: $F2, not $F$2
            fill=RED, font=Font(bold=True), stopIfTrue=True))
        ws.conditional_formatting.add(rng, FormulaRule(
            formula=[f"AND({open_and_due},$F{first}<TODAY())"],
            fill=AMBER, stopIfTrue=True))
        wb.save(dest)
    finally:
        wb.close()

if __name__ == "__main__":
    build(invoices, DEST)
    print(f"wrote {DEST}")

openpyxl assigns priorities in the order rules are added, starting at 1, and priority 1 is evaluated first. Adding the red rule first gives it precedence; stopIfTrue then prevents the amber rule from also applying to red rows. Without stopIfTrue, both fills would match and the higher-priority fill would still win, but fonts and borders from the lower-priority rule could combine with it — usually not what a reader expects.

Building the conditions from the same first variable keeps every reference in the formula anchored to the same row, which removes the off-by-one class of bug entirely.

Rule evaluation order for one row For each row Excel first evaluates the priority 1 rule, which tests whether the invoice is unpaid and more than 60 days past due. If true the row is filled red and stopIfTrue ends evaluation. Otherwise the priority 2 rule tests whether it is unpaid and past due at all, and fills the row amber. Rows that match neither keep their normal formatting. Priority 1: over 60 days AND(unpaid, TODAY()-$F2>60) → red fill, bold; stopIfTrue ends here Priority 2: past due AND(unpaid, $F2<TODAY()) → amber fill No match row keeps the cell styles written by pandas or the template

Variant Fix 1: The Same Rule with xlsxwriter

xlsxwriter uses the same reference semantics with two syntax differences: criteria formulas start with =, and rules are evaluated in the order they are added, just like openpyxl.

# pip install xlsxwriter "pandas>=2.2"
from pathlib import Path
import pandas as pd

def build_xlsxwriter(frame: pd.DataFrame, dest: Path, headroom: int = 300) -> None:
    last = len(frame) + 1 + headroom
    with pd.ExcelWriter(dest, engine="xlsxwriter",
                        datetime_format="yyyy-mm-dd") as writer:
        frame.to_excel(writer, sheet_name="Receivables", index=False)
        book, ws = writer.book, writer.sheets["Receivables"]
        red = book.add_format({"bg_color": "#FFC7CE", "bold": True})
        amber = book.add_format({"bg_color": "#FFEB9C"})
        base = 'AND($A2<>"",$E2<$D2)'
        ws.conditional_format(f"A2:F{last}", {"type": "formula",
                              "criteria": f"=AND({base},TODAY()-$F2>60)",
                              "format": red, "stop_if_true": True})
        ws.conditional_format(f"A2:F{last}", {"type": "formula",
                              "criteria": f"=AND({base},$F2<TODAY())",
                              "format": amber, "stop_if_true": True})

Variant Fix 2: Text Conditions and Case

Highlighting rows whose status is Disputed works with $G2="Disputed", and Excel's = comparison is case-insensitive, so disputed matches too. Partial matches need SEARCH (case-insensitive) or FIND (case-sensitive), wrapped in ISNUMBER because both return an error when there is no match:

# pip install "openpyxl>=3.1"
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill

GREY = PatternFill(start_color="E7E6E6", end_color="E7E6E6", fill_type="solid")

disputed = FormulaRule(formula=['$G2="Disputed"'], fill=GREY)
mentions_legal = FormulaRule(formula=['ISNUMBER(SEARCH("legal",$H2))'], fill=GREY)
# ws.conditional_formatting.add("A2:H500", disputed)

Inside openpyxl formulas, text literals use double quotes, so wrap the Python string in single quotes. Watch for trailing spaces in data typed by users: "Disputed " does not equal "Disputed". TRIM($G2)="Disputed" handles it, or clean the column with the approach from clean column names and whitespace in pandas before writing.

Reference styles and what they highlight With dollar F 2, absolute column and relative row, each row tests its own due date and whole rows are coloured correctly. With dollar F dollar 2, every row tests row 2 and all rows share one result. With F 2, the column drifts so each cell in a row tests a different column. With the formula written for row 1 on a range starting at row 2, each row is coloured by the row above it. Formula on A2:F500 Row behaviour Column behaviour Result $F2<TODAY() own row always F correct rows $F$2<TODAY() always row 2 always F all or nothing F2<TODAY() own row drifts right scattered cells $F1<TODAY() row above always F off by one

Keeping Highlights Correct as the Table Grows

Rules on a fixed range stop at their last row. When next month's report has 900 invoices instead of 300, the bottom rows are silently unformatted, and nobody notices because the rows that are formatted look right. Two approaches keep coverage honest.

The first is to compute the range from the data on every run, as the fix does with headroom, and to remove the previous run's rules before adding new ones so ranges do not accumulate. The second, for workbooks people extend by hand, is to put the data in an Excel table: when a user types in the row directly below a table, Excel extends the table, and conditional formatting applied to the table's full data range extends with it.

# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.formatting.formatting import ConditionalFormattingList
from openpyxl.worksheet.table import Table, TableStyleInfo

def refresh_rules(path: Path, sheet: str, rules: list, n_rows: int, last_col: str = "F") -> None:
    """Replace all row rules with fresh ones sized to the current data."""
    wb = load_workbook(path)
    try:
        ws = wb[sheet]
        ws.conditional_formatting = ConditionalFormattingList()        # drop last run's rules
        rng = f"A2:{last_col}{n_rows + 1}"
        for rule in rules:
            ws.conditional_formatting.add(rng, rule)
        if not ws.tables:
            table = Table(displayName="Receivables", ref=f"A1:{last_col}{n_rows + 1}")
            table.tableStyleInfo = TableStyleInfo(name="TableStyleLight1", showRowStripes=False)
            ws.add_table(table)
        wb.save(path)
    finally:
        wb.close()

Turn table row stripes off when using fill-based highlights: a striped table style competes visually with amber and red fills, and readers struggle to tell banding from status. More on tables, including structured references that make formulas readable, is in add Excel tables and named ranges with openpyxl.

Verification

Python cannot render conditional formatting, but it can compute which rows should be highlighted and compare that with a recalculated copy. LibreOffice in headless mode recalculates TODAY(), so exporting the sheet to CSV confirms formulas evaluate, while the Python-side expectation confirms the logic:

# pip install "pandas>=2.2" "openpyxl>=3.1"
from datetime import date
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

def expected_colours(frame: pd.DataFrame, today: date) -> list[str]:
    out = []
    for row in frame.itertuples():
        unpaid = row.paid < row.amount
        days = (pd.Timestamp(today) - row.due).days
        out.append("red" if unpaid and days > 60 else "amber" if unpaid and days > 0 else "none")
    return out

def verify_rules(path: Path, sheet: str) -> None:
    wb = load_workbook(path)
    try:
        rules = [(cf.sqref, r) for cf in wb[sheet].conditional_formatting for r in cf.rules]
        rules.sort(key=lambda pair: pair[1].priority)
        assert len(rules) == 2, f"expected 2 rules, found {len(rules)}"
        first, second = rules
        assert ">60" in first[1].formula[0] and first[1].stopIfTrue, "red rule must come first and stop"
        for sqref, rule in rules:
            assert "$F$" not in rule.formula[0], f"absolute row reference in {rule.formula[0]}"
            assert str(sqref).startswith("A2:"), f"range {sqref} should start at the first data row"
        print("rule order, anchoring and references verified")
    finally:
        wb.close()

if __name__ == "__main__":
    verify_rules(Path("out/receivables.xlsx"), "Receivables")
    frame = pd.read_excel("out/receivables.xlsx", sheet_name="Receivables")
    print(list(zip(frame["invoice"], expected_colours(frame, date(2026, 9, 17)))))

rule order, anchoring and references verified
[('INV-501', 'red'), ('INV-502', 'none'), ('INV-503', 'red'), ('INV-504', 'none')]

Compare the expected list with a visual check of the file on the same date. When a mismatch appears, the per-row expansion from the diagnostic almost always shows which reference is wrong.

FAQ

Can I highlight based on a value in another sheet? Yes, through a defined name or a lookup: COUNTIF(Blocked,$B2)>0 highlights customers listed in a Blocked named range. Direct sheet references work in current Excel but not in some older versions.

Why does the colour not update when the file is opened the next day? Excel recalculates TODAY() on open when calculation mode is automatic. If the workbook was saved in manual mode, press F9, or set wb.calculation.calcMode = "auto" and fullCalcOnLoad = True in openpyxl.

How do I colour alternating rows as well? Add a lower-priority rule MOD(ROW(),2)=0 with a light fill. Keep it last so status colours override the banding.

Do these rules slow down big workbooks? Each formula rule is re-evaluated across its range on recalculation. Two rules on a few thousand rows are negligible; dozens of rules on 100,000 rows are not.

Part of Conditional Formatting and Data Validation in Excel.