When I apply the conditional formats, I store it in the conditionalFormatCols and conditionalFormatRows variables. I then use the delete() method on these variables, but this doesn't delete the conditional formatting on the cells. How do I delete conditional formatting so it no longer shows up in the workbook?
let eventResult;
let navAidIsOn = false;
let conditionalFormatCols;
let conditionalFormatRows;
async function navAid() {
await Excel.run(async (context) => {
if (!navAidIsOn) {
applyConditionalFormatting();
const sheet = context.workbook.worksheets.getActiveWorksheet();
eventResult = sheet.onSelectionChanged.add(navAidListener);
navAidIsOn = !navAidIsOn;
} else {
turnNavAidOff();
}
});
}
async function turnNavAidOff(){
await Excel.run(eventResult.context ,async (context) => {
eventResult.remove()
Here I try to use the delete() method
conditionalFormatCols.delete();
conditionalFormatRows.delete();
await context.sync();
navAidIsOn = !navAidIsOn;
});
}
async function navAidListener() {
applyConditionalFormatting();
}
async function applyConditionalFormatting() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
let ranges;
let rangeArray = [];
await context.sync();
let columns;
let rows;
ranges = context.workbook.getSelectedRanges();
await context.sync();
ranges.load("address");
await context.sync();
let rangeStrs = ranges.address.split(",");
rangeStrs.forEach((rangeStr) => {
rangeArray.push(sheet.getRange(rangeStr).load("columnIndex, rowIndex, rowCount, columnCount"));
});
await context.sync();
rangeArray.forEach(async (range) => {
columns = sheet.getRangeByIndexes(0, range.columnIndex, 1048576, range.columnCount);
rows = sheet.getRangeByIndexes(range.rowIndex, 0, range.rowCount, 16384);
await context.sync();
conditionalFormatCols = columns.conditionalFormats.add(Excel.ConditionalFormatType.custom);
conditionalFormatCols.custom.rule.formula = "TRUE";
conditionalFormatCols.custom.format.fill.color = "#D2F0E0";
conditionalFormatRows = rows.conditionalFormats.add(Excel.ConditionalFormatType.custom);
conditionalFormatRows.custom.rule.formula = "TRUE";
conditionalFormatRows.custom.format.fill.color = "#D2F0E0";
await context.sync();
});
});
}