Conditional Formatting

Conditional formatting lets you apply styles to cells automatically based on their values. Define rules once, and the grid re-evaluates them whenever cell data changes — no manual restyling needed.

Note: Conditional Formatting is available in the Pro edition.

Rule types

ReoGrid supports three types of conditional formatting rules:

cellIs — value comparison

The most common rule type. Compare each cell’s value against one or two thresholds.

// Highlight scores >= 90 in green
worksheet.range('C2:C100').addConditionalFormat({
  type: 'cellIs',
  operator: 'greaterThanOrEqual',
  value1: 90,
  style: { backgroundColor: '#bbf7d0', color: '#14532d', bold: true },
})

// Highlight scores < 50 in red
worksheet.range('C2:C100').addConditionalFormat({
  type: 'cellIs',
  operator: 'lessThan',
  value1: 50,
  style: { backgroundColor: '#fecaca', color: '#7f1d1d', bold: true },
})

// Highlight values between 50 and 70 in yellow
worksheet.range('C2:C100').addConditionalFormat({
  type: 'cellIs',
  operator: 'between',
  value1: 50,
  value2: 70,
  style: { backgroundColor: '#fef3c7', color: '#92400e' },
})

Operators

expression — formula-based rules

Use a spreadsheet formula to decide whether the style applies. Relative cell references are offset automatically for each cell in the range.

// Highlight rows where Actual (col E) exceeds Target (col C)
// The formula is written for the first row of the range — ReoGrid offsets it for each subsequent row
worksheet.range('E2:E100').addConditionalFormat({
  type: 'expression',
  formula: '=E2>C2',
  style: { backgroundColor: '#86efac', color: '#14532d', bold: true },
})

The leading = is optional — 'E2>C2' works identically.

containsText — substring matching

Match text inside cell values. Case-insensitive by default (Excel-compatible).

// Highlight cells containing "Cancelled"
worksheet.range('D2:D100').addConditionalFormat({
  type: 'containsText',
  operator: 'contains',
  text: 'Cancelled',
  style: { backgroundColor: '#fee2e2', color: '#991b1b' },
})

Text operators

Set caseSensitive: true to enable case-sensitive matching.

Priority and stopIfTrue

When multiple rules apply to the same cell, priority controls evaluation order (lower number = evaluated first). Use stopIfTrue to prevent lower-priority rules from being evaluated when a higher-priority rule matches.

// Traffic-light pattern: first match wins
worksheet.range('C2:C100').addConditionalFormat({
  type: 'cellIs', operator: 'lessThan', value1: 30,
  priority: 1, stopIfTrue: true,
  style: { backgroundColor: '#fecaca' },  // red
})
worksheet.range('C2:C100').addConditionalFormat({
  type: 'cellIs', operator: 'lessThan', value1: 70,
  priority: 2, stopIfTrue: true,
  style: { backgroundColor: '#fef3c7' },  // yellow
})
worksheet.range('C2:C100').addConditionalFormat({
  type: 'cellIs', operator: 'greaterThanOrEqual', value1: 70,
  priority: 3,
  style: { backgroundColor: '#bbf7d0' },  // green
})

Applying rules

Range API (recommended)

const id = worksheet.range('B2:B50').addConditionalFormat({
  type: 'cellIs',
  operator: 'greaterThan',
  value1: 1000,
  style: { bold: true, color: '#16a34a' },
})

Cell API

worksheet.cell('A1').addConditionalFormat({
  type: 'cellIs',
  operator: 'equal',
  value1: 'Done',
  style: { backgroundColor: '#dcfce7' },
})

Worksheet API

worksheet.addConditionalFormat(1, 1, 50, 3, {
  type: 'cellIs',
  operator: 'greaterThan',
  value1: 100,
  style: { backgroundColor: '#dbeafe' },
})

Removing rules

// Remove a specific rule by ID
worksheet.removeConditionalFormat(id)

// Clear all rules in a range
worksheet.range('B2:B50').clearConditionalFormats()

// Clear all rules on the worksheet
worksheet.clearConditionalFormats()

Listing rules

const rules = worksheet.getConditionalFormats()
rules.forEach(entry => {
  console.log(entry.id, entry.range, entry.rule.type)
})

Supported style properties

Conditional format styles support the following properties. These are the same properties that round-trip through xlsx export/import.

Other CellStyle properties are accepted at runtime but are not persisted in xlsx files.

Per-side border overrides

In addition to a style payload, a rule can carry a border payload that paints right / top / bottom / left edges on matching cells. This overrides manual borders on a per-side basis — useful for highlighting outliers or thresholds with a coloured edge while leaving the rest of the cell styling intact.

// Draw a thick red right border on cells where the value exceeds the threshold
worksheet.range('B2:B100').addConditionalFormat({
  type: 'cellIs',
  operator: 'greaterThan',
  value1: 1000,
  border: {
    right: { style: 'medium', color: '#dc2626' },
  },
})

// All four sides
worksheet.range('D2:D50').addConditionalFormat({
  type: 'expression',
  formula: '=D2<0',
  border: {
    top:    { style: 'thin', color: '#7f1d1d' },
    bottom: { style: 'thin', color: '#7f1d1d' },
    left:   { style: 'thin', color: '#7f1d1d' },
    right:  { style: 'thin', color: '#7f1d1d' },
  },
})

The style payload and border payload can be combined — both apply when the rule matches.

xlsx round-trip

Conditional formatting rules are preserved when importing and exporting .xlsx files. Rules are written as standard <conditionalFormatting> elements in the worksheet XML, with styles stored in the <dxfs> table. Per-side border payloads round-trip through <dxf><border> — fully compatible with Excel.