Google Spreadsheet: Script to Change One Cell's Background Color when Another Cell Changes Text

Question

I have a Google spreadsheet where I keep a list of tasks, and I constantly change the status from four variations. For the purpose of this questions lets just say the four statuses are "Done", "Not Done", "In progress", "Failed".

I want to write a script for the Google spreadsheet where when I change the status, the cell right next to it will display a specific color respective to the status of each task.

I have looked into a few of the previous example where they highlight the whole row, but I'm looking into how to highlight one specific cell.

Answer 1

This can be accomplished with conditional formatting directly in the spreadsheet.

Right click on the first cell you want to change the color and choose "Conditional Formatting"

In the drop-down choose Custom formula is... Then enter =(A2 = "Done"). Check background color select then select a color. The range should be auto filled.

A2 would be whatever your status cell is.

Repeat this for each status you have.

You then can "Highlight and Drag-Copy" the cell with the formatting and it will adjust the range for you.

Answer 2

I'm new here, so please forgive any breaches in forum etiquette, but another user posted a similar question recently, and user Zwisch answered it using a loop that I think might also be applicable to what you are trying to accomplish.

You can likely adapt his answer to your needs by adding another conditional statement to address your different statuses. I do not yet have enough expertise to provide you an exact adaptation however.

See Zwisch's answer to how to apply conditional formatting using code.