CustomConditionalFormat applied to a RangeAreas behaves differently depending on the Range used to fetch the ConditionalFormat

[sndurkin]

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: 16.0.18227.20152
• Operating System: Windows 10

Expected behavior

When updating the formula for a CustomConditionalFormat applied to a RangeAreas, I expected the formula to be applied as-is, without adjustment. In the example ScriptLab script, the formula I provide is =C1 = 1. I expected it to remain that after applying it.

Current behavior

The formula is adjusted to =A1 = 1 after applying it. I believe this happens because the CustomConditionalFormat is applied to a RangeAreas that is composed of both C1 and E1, but the CustomConditionalFormat instance is fetched from cell E1 (the 2nd Range from the RangeAreas) rather than from cell C1.

Steps to reproduce

1. Import and run the provided ScriptLab script.
2. Click the Setup button, which will create a CustomConditionalFormat for cells C1 & E1, highlighting cells in green if they equal 1 using formula =C1 = 1.
3. Click the Run button, which should fetch the CustomConditionalFormat from cell E1 and set the exact same formula, =C1 = 1 again.

Link to live example(s)

https://gist.github.com/sndurkin/a45541e3f07d791996e4efa33cabe1a0

Provide additional details

N/A

Context

I would expect the CustomConditionalFormat to behave the same regardless of which Range within the RangeAreas it was fetched from, presumably because it's the exact same conditional format. I don't really have a workaround for this, because even if I derived the first Range associated with the RangeAreas for the CustomConditionalFormat, I wouldn't necessarily know which CustomConditionalFormat from the first Range is the same as the one I originally fetched, because there is no globally unique id I can use to compare them.

Useful logs

[RuizhiSunMS]

Hi @sndurkin, thx for reaching out here.
Would you please try to add formula like "=$c$1 =1"? This change works for me.

[sndurkin]

Hi @RuizhiSunMS, thanks for the response. I don't think that will work because that's fundamentally a different formula. My original formula says: If any cell in the range(s) equals 1, highlight it green

[RuizhiSunMS]

@sndurkin, I think I got your point. Mark it as #9626786 into our backlog. I will involve experts to discuss this behavior. Will reply here if any update.

[qinliuMSFT]

Hi @sndurkin ,

Would you please try the following script? I changed to formulaR1C1: "=RC = 1" ,which means each cell in that range will check if its own value is 1.

async function run() {
  await Excel.run(async (context) => {
    // Reproduce bug
    let sheet = context.workbook.worksheets.getItem("Sheet1");
    let E1 = sheet.getRange("E1");
    let cf = E1.conditionalFormats.getItemAt(0);
    cf.load("custom/rule");
    ranges = cf.getRanges();
    ranges.load("address");
    await context.sync();

    console.log(ranges.address);
    console.log(cf.custom.rule);

    cf.setRanges(ranges);
    cf.custom.rule.formulaR1C1 = `=RC = 1`;
    await context.sync();

    cf.load("custom/rule");
    await context.sync();
    console.log(cf.custom.rule);
  });
}

[sndurkin]

Hi @qinliuMSFT, that does seem to work. AFAIK the Excel.Application.ConvertFormula API isn't ported to the JS API yet, so I'll use another worksheet to convert the A1-style formula that I have into R1C1 (by setting Range.formulas then reading Range.formulasR1C1) so I can utilize this workaround. Thanks!

I would still argue that this bug should stay open; IMO the CustomConditionalFormat object should behave the same regardless of which cell is used to fetch it.

[qinliuMSFT]

Hi @sndurkin, Happy New Year!

The behavior you're observing is by design due to how Excel handles relative references in conditional formatting rules. Also, the logic for getting formulas and putting formulas is slightly different. When you get the formula from a conditional format, it retrieves the formula relative to the top-left cell of the range. However, when you set the formula, it applies the formula relative to the current range.
When you retrieve the conditional format from E1 and reapply the rule = C1 = 1, Excel again interprets this formula relative to E1 (the current range in context). This means that the formula is now interpreted as true if the R(0)C(-2) = 1. You can try changing the formula to =E1=1 at this time, and you will find that it will become =C1=1 in the end, because it is relative to E1, not C1.

So, to maintain the same conditional formatting rule, the recommended approach is:

• Get the range from the conditional format,
• Get the first address of the range,
• Then retrieve the conditional format again from the refreshed range and then apply the rule.

async function update() {
  await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sheet1");
    let E1 = sheet.getRange("E1");
    let cf = E1.conditionalFormats.getItemAt(0);
    cf.load("custom/rule");
    let ranges = cf.getRanges();
    ranges.load("address");
    await context.sync();
    console.log(ranges.address);
    console.log(cf.custom.rule);

    let firstAddress = ranges.address.split(",")[0];
    let firstRange = sheet.getRange(firstAddress);
    let cfFromFirstRange = firstRange.conditionalFormats.getItemAt(0);
    cfFromFirstRange.custom.rule.formula = `=C1 = 1`;
    await context.sync();

    cfFromFirstRange.load("custom/rule");
    await context.sync();
    console.log(cfFromFirstRange.custom.rule);
  });
}

This approach and the formulaR1C1 I mentioned last time are both the approaches we advocate, please let us know if you have any other questions.

[sndurkin]

Hi @qinliuMSFT, thanks for the detailed response, the behavior is understandable. I've implemented the formulaR1C1 approach and it's working great.