How to drag down or copy a conditional formatting rule

How to drag down or copy a conditional formatting rule

Posted by: richard on 25 June 2025, 5:58 pm EST

• 

  richard

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 25 June 2025, 5:58 pm EST - Updated 25 June 2025, 6:04 pm EST

  If I have created a conditional formatting rule for cell A1, which is “some value between B1 and B2”, then I want to drag that rule down such that A2 has “some value between B2 and B3” etc…

  It seems like the rule is locked to B1 and B2 no matter what. Is there a way to create relative rules?

  If not, what is the best way for creating a rule that can operate relative to the cell.

  I have attached a zip with a between rule (though I didn’t know how to create one via JS that had cell references so it needs to be edited in the UI). If you take the initial rule and update it to match my screenshot it may help convey what I’m trying to do.

  conditional-format.zip

  

  1045×710 70.4 KB

• 

  chirag.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 26 June 2025, 1:28 am EST - Updated 26 June 2025, 1:33 am EST

  Hi,

  The described behavior—where a conditional formatting rule using cell references like B1 and B2 remains fixed when dragged or applied to other cells—is expected when the rule is created using the built-in “Between” condition, as it treats referenced cells as absolute by default. The same behavior is also reproduced in Microsoft Excel.

  To achieve a relative rule that updates per row (e.g., A2 compares with B2 and B3), a formula-based rule should be used. This allows cell references to adjust automatically for each row in the target range.

  Please refer to the code snippet below using the SpreadJS API to apply the required rule:

  var rule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule();
  rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.formulaRule);
  rule.ranges([new GC.Spread.Sheets.Range(0, 0, 5, 1)]);
  rule.style(style);
  rule.formula("=AND(A1>B1,A1<B2)");
  sheet.conditionalFormats.addRule(rule);
  

  The conditional formula will update dynamically for each row when the range is dragged or applied.
  
  A sample code file demonstrating this behavior is also attached for reference.

  Please feel free to reach out in case of any further questions or if additional assistance is required.

  Best Regards,
  
  Chirag Gupta

  Attachment: conditional-format.zip

  

• 

  richard

  • Post Options:
  • Link
    Copy

  Posted 26 June 2025, 11:13 am EST

  Brilliant, thank you so much