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.


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

| improve this answer | |

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

| improve this answer | |
  • Thank you, that's what I suspected. Is there another way to achieve the same effect? – Will M 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 – Will M Feb 21 '19 at 3:03
  • yup, it got updated but still not possible to control it via formula. that link has another link: webapps.stackexchange.com/questions/48783/… and yes your assumption is correct and you would need to gain som knowledge of JavaScript to write your own script – user0 Feb 21 '19 at 3:25

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.