Jump to content

How can I format a calculated cell to change color depending on the value?

Kimberley Stewart

Recommended Posts

Kimberley Stewart

I am working on a spreadsheet where I have 3 text boxes. I would like the 3rd text box to show the product of the first 2 and then change color depending on this number.

i.e. Text1 : 5,  Text2: 2,  Text3:(5*2) = 10

I would like to have 3 color changes for Text3:

Green if the number is between 1 - 6

Yellow if between 7 - 14

Red if between 15 - 25

Could anyone help me with what the Javascript would be? I have tried a few things unsuccessfully. If I put the following into the Custom calculation script on Text3, the color changes but I lose the multiplication calculation so the number has to be manually calculated and typed in to see the change in color:

var v = Number(event.value);
if (v>=1 && v<=6) {event.target.fillColor = color.green;}
else if (v>6 && v<=14) {event.target.fillColor = color.yellow;}
else if (v>14 && v<=25) {event.target.fillColor = color.red;}
else event.target.fillColor = color.white;


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.