8

In Conditional Formatting in Google Sheets, I'm wondering if it is possible to combine the Format Cells if - Custom Formula is with a Colour Scale?

It would appear not, but is there a clever workaround to achieve it?

I have a Custom Formula which returns a numerical value between 1 and 100. I then want to assign a different cell colour for each number on a colour scale from Red to Green via Mid Point of Grey.

So 1 = Red, 50 = Grey, and 100 = Green: with all the gradations in between.

I could spend time creating 100 unique rules - one for each number/colour - but it would be much simpler to use a colour scale.

I have a feeling I may need to use an Apps Script, but I don't know how.

6

This is now possible by inserting a formula instead of a static number into the box for min/mid/max amount. enter image description here

1
  • I tried usisng this approach to format a range based on the sum of its values, for example format I2:M5 setting minpoint to =sum($I2:$M2)=1 with increasing values for midpoint and maxpoint. The result: cells with values all formatted as maxpoint, blank cells all unformatted. Take a look at (docs.google.com/spreadsheets/d/…) Jun 27 at 7:33
2

No, this is not possible. Color Scale conditional formatting can't be managed by formula. This is possible only in Single Color conditional formatting.

7
  • Thank you, that's what I suspected. Is there another way to achieve the same effect?
    – TopMarx
    Feb 21 '19 at 2:16
  • it is but you won't like it... setup 100+ Single Color conditional formattings :D
    – user0
    Feb 21 '19 at 2:29
  • also, Color Scale conditional formatting is able to be manipulated from script, like: stackoverflow.com/questions/19637514/…
    – user0
    Feb 21 '19 at 2:32
  • Thank you again, I looked at that question but it seems Google Sheets has been updated since then with the "Colour Scale" functionality. Also, I'm not familiar with Apps Script to fully understand the answer. I presume I would somehow have to incorporate my custom formula into the script? I did try to play around with this but I got an error: "You do not have permission to call setBackgrounds". Thank you for your help
    – TopMarx
    Feb 21 '19 at 3:03
  • 1
    It is now possible; see the other answer. Jul 17 '20 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.