Error while manipulating Conditional Formatting

[fiorellino]

Provide required information needed to triage your issue

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: PC desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: 2411 (Build 18227.20162 Click-to-run)
• Operating System: Win 11

Expected behavior

The command context.workbook.worksheets.getActiveWorksheet().getRange().conditionalFormats.getItemAt(0).delete(); can be executed without producing errors, when at least one conditional formatting is available in the current worksheet.

Current behavior

The following error is produced: RichApi.Error: Item Not Found.

Steps to reproduce

1. Open new workbook
2. Using Excel interface, choose Conditional Formatting > Manage Rules > "Show formatting rules for Current Selection". Then, use "New rule..." > "Format only cells that contain" twice, create two conditional formattings rules on the same cell (say, A1), for example:
   a. Set blue background when cell value is between 1 and 2;
   b. Sed red background when cell value is between 3 and 4.
3. Dismiss the dialog by pressing "Ok".
4. In Script Lab, execute the following function

async function run() {
  await Excel.run(async (context) => {
    context.workbook.worksheets.getActiveWorksheet().getRange().conditionalFormats.getItemAt(0).delete();
    await context.sync();
  });
} 

6. Pressing "Run", the error RichApi.Error: Item Not Found. is thrown.

[RuizhiSunMS]

Hi @fiorellino, thx for reaching out here. I can repro it and I want to confirm with you that:

1. only win32 would repro this. web works well
2. only repro with rules created by 'manage rules' dialog, but not by js api

Put it as #9647495 into our backlog. I will involve our experts to investigate. Please be impatient and we will reply here if any update.

[fiorellino]

Hi @RuizhiSunMS, thank you for your quick response.
I can confirm both 1 & 2.
Should it help, another scenario in which the bug can be reproduced - without using the 'manage rules' dialog - is the following one:

1. open the attached workbook ExampleFile.xlsx (with Excel for Windows), whose unique worksheet carries three conditional formatting rules;
2. execute twice the script run above:

async function run() {
  await Excel.run(async (context) => {
    context.workbook.worksheets.getActiveWorksheet().getRange().conditionalFormats.getItemAt(0).delete();
    await context.sync();
  });
} 

3. the first execution succeeds, deleting the first of the three conditional formatting rules, whereas the second execution throws the RichApi.Error: Item Not Found. error.