Google Spreadsheet : Formatting background color based on cell value

0
votes

I'm trying to color the background of a bunch of cells based on their value and a specified range.

So lets say the range is 1.0-3.0, then the cells with 3.0 are colored black and the ones with 1.0 are colored white with cells having intermediate value getting a shade of grey on a linear scale.

I'm sure scripting is involved. Any help would be appreciated

2
google-apps-scriptgoogle-sheetsgs-conditional-formatting

2 Answers

1
votes

A guy has written a sheet which does exactly what you need.

(original post: http://www.andrewroberts.net/2013/12/google-sheet-color-scales/)

https://docs.google.com/spreadsheet/ccc?key=0AhRtIprIrwuzdFNJNHQ5MDZtb0RseGduNEhBdXE2TlE&usp=sharing#gid=0

Usage:

  • Follow the link to view a read-only copy of the spreadsheet.
  • Login with your own Google account (Sign-in top right-hand corner).
  • Make a copy of the spreadsheet (File>Make a copy…).
  • The copy will appear in the root of your Google Drive from where you can open it.
  • Select a range of numbers and click on Color Scale>Color Scale Selection.
  • To change the low and high colors click on Color Scale>Choose Colours and add your own colors as three decimal RGB values between 0 and 255.
0
votes

Sheets now has a Colour scale feature.

Say your data is in A2:G2. Select the range, Format, Conditional formatting..., Colour scale, for Minpoint select Number and enter 1 with white fill and for Maxpoint select Number and enter 3 with black fill, Done:

SO24791137 example