Conditional formatting with color scale for many rows independently once

Question

I create reports with different cohort tables. Each row of a table must have conditional formatting with color scale (depending only on current row values, i.e. regardless of other rows min/max values). Are there possibilities (in the GUI or using some scripts) to make such formatting quickly?

At the moment, I add conditional formatting rule for each rows independently, it is very tediously and time-consuming!

I tried to find a solution on this and other websites, but found no such questions. I hope an answer exists, and someone will share it.

---

Update

What I need is creating multiple different color scale ranges on different rows independently but simultaneously:

---

Update 2

@OrenPinsky's solution is almost perfect! However, it takes only initial position for formatting and then goes down through all the rows until a blank one. It doesn't use selected row width, nor table height. So, better answers will be considered and appreciated as well.

Answer 1

I know I'm a bit late to the party... but I had this same question myself and then ended up finding the answer on my own with a little tinkering.

Basically, you just need to use "Number" for your min/mid/max values and then enter a formula with a partially locked reference (i.e. add a "$" before the column/row depending on your needs).

In my case I needed to values to reference on a "per row" basis, so I used "$F2" to ensure the reference value for each cell only changed as I shifted rows--but would remain the same across all columns in a given row. (So B2:E2 all reference F2, but B3:E3 instead reference F3.)

Hopefully that makes sense?

Example:

If you're not trying to compare to a fixed value, you can also use =MIN(), =AVERAGE(), and =MAX() to set your points like in the example below (which also serves as an example of assessing "by column" instead of "by row")

^Is a bit cut off, so to clarify "=MIN(O$3:O$7)" was used to ensure the reference shifts to "MIN(P3:P7)" for the next column.^

Answer 2

on your spreadsheet, record any macro and create a keyboard shortcut for it. Then go to Tools->Macros->Manage macros and change the macro code to the following one (you might need to change the function name to the same of your macro).

/** @OnlyCurrentDoc */

function xxx() {

  var spreadsheet = SpreadsheetApp.getActive()

  do {
    spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();

    conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();
    conditionalFormatRules.push(SpreadsheetApp.newConditionalFormatRule()
      .setRanges([spreadsheet.getActiveRange()])
      .setGradientMaxpoint('#57BB8A')
      .setGradientMidpointWithValue('#FFFFFF', SpreadsheetApp.InterpolationType.PERCENTILE, '50')
      .setGradientMinpoint('#E67C73')
      .build());
    spreadsheet.getActiveSheet().setConditionalFormatRules(conditionalFormatRules);

    spreadsheet.getActiveCell().activate().offset(1,0).activate()

  }

  while (spreadsheet.getActiveCell().isBlank() == false)

};

Then, select the range on which you want to do the condition formatting and type your keyboard shortcut (on the example file it is ctrl-alt-shift 1)

Voila!