Using setConditionalFormatRules() in google sheets to highlighte a row

Question

I have a conditional fomatting applyed using "whenTextEqualTo" case. This works fine and the cell, which meets this conditions highlighted as desired. However, I would like to have the row to be highlighted , in which this cell is found.

So, the sheet FinData has data located in range from A2 to G500. The value I am checking is in the column F (value should be == "CDI"). When this is met, the cell is highlighted . How to do the same but with the row being highlighted , i.e. col B to col G inclusively. I have checked this post but could not incorporate it into mine below:

function formatCDI() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var finDataSheet = ss.getSheetByName("FinData");
  var range = finDataSheet.getRange("A2:G500");
  finDataSheet.clearConditionalFormatRules();

  var rule = SpreadsheetApp.newConditionalFormatRule()

    .whenTextEqualTo("CDI")
    .setBackground("#86D3FF")
    .setRanges([range])
    .build();
  var rules = finDataSheet.getConditionalFormatRules();
  rules.push(rule);
  finDataSheet.setConditionalFormatRules(rules);
};

Would apprecaite any help on this. Thank you,

Tanaike · Accepted Answer · 2019-03-14 09:21:35Z

2

You want to apply the highlight for the column from "B" to "G" using the conditional formatting, when the value of column "F" is CDI.

If my understanding is correct, how about using whenFormulaSatisfied() instead of whenTextEqualTo(). In your case, as the formula, =$F2="CDI" is used.

Modified script:

Please modify as follows.

From:

var range = finDataSheet.getRange("A2:G500");

And

.whenTextEqualTo("CDI")

To:

var range = finDataSheet.getRange("B2:G500");

And

.whenFormulaSatisfied('=$F2="CDI"')

Reference:

whenFormulaSatisfied(formula)

If I misunderstood your question, please tell me. I would like to modify it.

edited Mar 14, 2019 at 9:21

answered Mar 14, 2019 at 8:34

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AlexShevyakov Over a year ago

All is correct - thank you! Of course ".whenFormulaSatisfied" is the one to go! Apprecaite it! –

Tanaike Over a year ago

@AlexShevyakov Thank you for replying. I'm glad your issue was resolved. Thank you, too.

Collectives™ on Stack Overflow

Using setConditionalFormatRules() in google sheets to highlighte a row

1 Answer 1

Modified script:

From:

To:

Reference:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

From:

To:

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related