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, });
}
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;
}