How to change cell color in Google Sheets based on checkbox value?

Google Sheets provides a variety of useful features for customizing your spreadsheets. One handy option is the ability to change a cell’s background color based on the value in another cell. For example, you may want to highlight cells red if a checkbox is checked, and green if it’s unchecked. Here’s how to do it.

Use Conditional Formatting

The easiest way to change a cell’s color based on a checkbox value is with Conditional Formatting. This allows you to set rules that will automatically apply formatting like color fills to cells when certain conditions are met.

Here are the steps to configure it:

Click on the cell(s) you want to format.
Go to the Menu bar and select Format > Conditional formatting.

Click the drop down for “Format cells if…” and choose “Custom formula is”.
Enter a formula like =$A$1=TRUE to format if checkbox in cell A1 is checked.
Click the Format button and choose the fill color you want (e.g. red).

Click Done to save the rule.

Now the selected cells will turn red automatically whenever the checkbox is checked. You can add other rules for different checkbox values too.

Use a Script

For more advanced conditional cell coloring, you can use a custom Google Apps Script. This gives you greater flexibility to apply colors not just based on checkboxes, but any values or formulas.

Here are the steps to set it up:

Open your spreadsheet and select Extensions > Apps Script.

Copy this script into the Code.gs editor:

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  const checkbox = sheet.getRange('A1'); //checkbox cell
  const colorCells = sheet.getRange('B1:B10'); //cells to color

  let checkboxValue = checkbox.getValue();

  if(checkboxValue === true) {
    colorCells.setBackground('#ff0000'); //red
  } else {
    colorCells.setBackground('#00ff00'); //green
  } 
}

Click Save and give authorization if prompted.
Select the checkbox cell and the cells to color.
Edit the code to reference your cells.

Click the play button to test it.

This will run each time the checkbox value changes, and color the selected cells red or green based on whether it’s checked or not.

Use Data Validation

You can also change cell colors by setting Data Validation rules based on a checkbox. Follow these steps:

Select the cells you want to color.
Go to Data > Data Validation.
Set the Criteria to “List from a range” and enter your checkbox cell.

Tick the “Show warning” and “Reject input” checkboxes.
Click Save.

Now when the checkbox is checked or unchecked, it will validate the cell colors and show a warning if invalid. To set the colors:

Select the cells and go to Format > Conditional formatting.
Enter a formula like =$A$1=TRUE for checked.
Choose the color fill and click Done.

Add another rule for unchecked with =$A$1=FALSE.

This will dynamically color the cells based on the checkbox validation rules.

Use Cell Linking

You can also directly link another cell value to control the background color. Do this by:

Select the cell(s) you want to color.
Click the data validation button (in Data tab).
On the Criteria tab, change Allowed Value to “Cell link”.

Enter your checkbox cell reference.
Click Save.

Now the selected cells will automatically match the color of the checkbox cell. If you want to customize the colors:

Select the checkbox cell.
Go to Format > Conditional formatting.
Add rules for colors on TRUE and FALSE values.

The linked cells will assume the same colors when the checkbox changes.

Use the TERNARY Function

You can also return a color directly from the checkbox value using the TERNARY function:

=TERNARY($A$1, "#FF0000", "#00FF00")

This will output the hex code for red if the checkbox is checked, or green if unchecked. To use:

Select the cell to color.
Enter the TERNARY formula referencing the checkbox.
Make sure the cell format is set to custom color.

The cell will now automatically change between the specified colors when the checkbox state changes.

Use Color Scales

For a more gradual color change, you can use Color Scales conditional formatting based on the checkbox value. Do this by:

Select the cells to format.

Go to Format > Conditional Formatting > Color Scales.
On thecolor ramp, double click the beginning and end colors.
Set one to a color for checked state, the other for unchecked.

Enter the checkbox reference cell.
Click Done.

This will blend the cell colors between the two chosen hues based on whether the checkbox is checked or not.

Use IF Functions

You can also embed IF functions that return a color code depending on the checkbox state.

For example:

=IF(A1=TRUE,"#FF0000","#00FF00")

Returns red if A1 is checked, or green if unchecked.

To apply:

Select the cell to format.
Add the IF function referencing the checkbox cell.

Make sure the cell format is set to custom color.

The cell will change color automatically when the checkbox value changes.

You can build on this with additional nested IFs to support multiple conditional colors:

=IF(A1=TRUE,"#FF0000",IF(A2=TRUE,"#0000FF","#00FF00"))

Now the cell color will be red, blue or green depending on A1 and A2.

Use the CHOOSE Function

The CHOOSE function can also return a color code from a set of options based on the checkbox state.

For example:

=CHOOSE(A1+1,"#FF0000","#00FF00")

This will return red if A1 is checked (1), or green if unchecked (0).

To implement:

Select the cell to format.

Add CHOOSE formula referencing checkbox.
Set cell format to custom color.

You can add more comma-separated color codes to support additional checkbox states.

The key is converting the checkbox boolean to a numeric index for CHOOSE.

Use Images for Colors

Instead of color fills, you can also conditionally show images in cells to represent different checkbox states:

Insert checkbox images for each state into the sheet, sized to cells.

Give them names like CheckedImage or UncheckedImage.
Use =IMAGE() formula referencing the named images based on checkbox value.

For example:

=IMAGE(IF(A1=TRUE,CheckedImage,UncheckedImage))

This will show the checkbox checked or unchecked image based on A1.

The benefit over color fills is you can use any images, not just colors.

Sample Code for Reference

Here are some code snippets you can reference to conditionally color cells based on checkbox values:

Apps Script

function onEdit(e) {
  
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getActiveSheet();

  const checkbox = sheet.getRange('A1');
  const colorCells = sheet.getRangeList(['B1','C1','D1']);

  let checkboxValue = checkbox.getValue();

  if(checkboxValue == true) {
    colorCells.setBackground('#ff0000');
  } else {
    colorCells.setBackground('#00ff00');
  }
}

Conditional Formatting Formula

=IF($A$1=TRUE,$D$1,$E$1)

Applies different background fills from D1 and E1 based on A1 checkbox.

TERNARY Function

=TERNARY(A1=TRUE,"#FF0000","#00FF00")

Returns red or green color code based on A1 checkbox state.

IF Formula

  
=IF(A1, "Yes", "No")

Returns “Yes” if A1 is checked, “No” if unchecked.

VLOOKUP with Data Validation

=VLOOKUP(A1,Data!A1:B2,2,FALSE)

Looks up color code from table Data!A1:B2 based on A1 checkbox state.

Conclusion

In summary, there are a variety of ways to dynamically change cell colors based on checkbox values in Google Sheets, including:

Using Conditional Formatting rules

Writing custom Apps Scripts
Leveraging Data Validation
Linking the checkbox cell

Embedding IF formulas
Utilizing the TERNARY and CHOOSE functions
Showing images with IMAGE() formula

The best approach depends on exactly what you want to do. But these tips should provide a good starting point for conditionally formatting cells based on checkboxes to visualize data changes.

Let me know if you have any other questions!