exceloffice-jsoffice-addinsexcel-addinsexcel-web-addins

Apply Conditional Formatting - Duplicates - Excel Javascript


I'm trying to add Conditional Highlighting to a column in Excel via Office-JS. I used the automate feature for the first time, but I can't seem to get it to work in Excel.

This is what automate spat out:

function main(workbook: ExcelScript.Workbook) {
    let conditionalFormatting: ExcelScript.ConditionalFormat;
    let selectedSheet = workbook.getActiveWorksheet();
    // Change preset criteria from range A:A on selectedSheet
    conditionalFormatting = selectedSheet.getRange("A:A").addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
    conditionalFormatting.getPreset().getFormat().getFont().setColor("#9C0006");
    conditionalFormatting.getPreset().getFormat().getFill().setColor("#FFC7CE");
    conditionalFormatting.getPreset().setRule({criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues,});
}

This is my attempt at transcribing:

function Do_ApplyHighlightDupsConditionalFormatting(rng) {
    ConditionalFormat.rng.addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
    ConditionalFormat.getPreset().getFormat().getFont().setColor("#9C0006");
    ConditionalFormat.getPreset().getFormat().getFill().setColor("#FFC7CE");
    ConditionalFormat.getPreset().setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues, });
}

Solution

  • After transposing the MS Docs and @BrianGonzalez Typescript answer, I got it working!

    Func:

    function Set_Dup_Conditional_Formatting(rng) {
        //https://stackoverflow.com/questions/71189904/apply-conditional-formatting-duplicates-excel-javascript
        var conditionalFormat = rng.conditionalFormats.add(
            Excel.ConditionalFormatType.presetCriteria
        );
        conditionalFormat.preset.format.font.color = "red";
        conditionalFormat.preset.rule = {
            criterion: Excel.ConditionalFormatPresetCriterion.duplicateValues
        };
        return true;
    }