14

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.

Improve this question

2 Answers 2

Reset to default
10

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
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/…)
    – David Knapp
    Jun 27, 2021 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.

Improve this answer
7
  • Thank you, that's what I suspected. Is there another way to achieve the same effect?
    – TopMarx
    Feb 21, 2019 at 2:16
  • it is but you won't like it... setup 100+ Single Color conditional formattings :D
    – user0
    Feb 21, 2019 at 2:29
  • also, Color Scale conditional formatting is able to be manipulated from script, like: stackoverflow.com/questions/19637514/…
    – user0
    Feb 21, 2019 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, 2019 at 3:03
  • 2
    It is now possible; see the other answer.
    – krookedking
    Jul 17, 2020 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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