[SOLVED] Excel Conditional Formatting - creating an Office script

Excel Conditional Formatting - creating an Office script

I have a small number of conditional formatting rules shown in the image below.

Formatting Rules Screenshot

I have 4 conditional formatting rules in Excel but i'd like to automate them using Typescript.

What i'm having trouble with is format the colour of a row using Typescript , based on two cells in the row.

eg, If column $B1 contains "Y(UP)KZR" and column $G1 contains "DN to UP" then highlight that row.

I can then duplicate the code for the three other rules i'm looking to automate.

Solution

Use getCustom().getRule().setFormula() to setup CF formula
getAddress() returns a reference with the sheet name. Transformation is needed for the CF formula.

function main(workbook: ExcelScript.Workbook) {
    // the used cells range
    // let selectedRange = workbook.getActiveWorksheet().getUsedRange();
    // all cells
    let selectedRange = workbook.getActiveWorksheet().getRange();
    let positiveChange = selectedRange.addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
    // modify the filling color as needed
    positiveChange.getCustom().getFormat().getFill().setColor("lightgreen");
    const cellB = "$" + selectedRange.getCell(0, 1).getAddress().split("!")[1];
    const cellG = "$" + selectedRange.getCell(0, 6).getAddress().split("!")[1];
    const cfFormual = `=AND(${cellB}="Y(UP)KZR",${cellG}="DN to UP")`;
    positiveChange.getCustom().getRule().setFormula(cfFormual);
}