What you are describing should be able to be done through the conditional formatting interface: Format > Conditional Formatting
You can add multiple condition rules for a given range so you would add
- Text is exactly: "in-progress" (case sensitive); Format: background color green
- Text is exactly: "for review" (case sensitive); Format: background color blue
- Text is exactly: "Done" (case sensitive); Format: background color grey
When using conditional formatting, I find it best to use data validation as well (Data > Validation). If you choose the validation criteria as "list of items" and choose the option to display in-cell button, you can create a dropdown menu in each cell for your range, keeping you from having to type it each time
If you are absolutely set on creating a formula from scratch using apps script, take a look at this post on a similar topic and see if it could be adapted for your purposes. This is probably not the most effiect way to tackle the problem however.
As another thought, it sounds like you may be trying to create some kind of version of a work tracker. For something a little more intuitive, you may want to consider a Kanban app such as the free Kanbanchi which integrates with Google Drive nicely.
Edit:
With the new google sheets, you are able to do conditional formatting based on a custom formula. This means that you can base the color of a range based on a specific cell reference. Here are the 3 custom Formulas you could use:
- =IF($A$2 = "In Progress", 1,0)>0
- =IF($A$2 = "For Review", 1,0)>0
- =IF($A$2 = "Done", 1,0)>0
Where $A$2 is the cell for reference that will contain the status. The only downside to this method is that you have to set the formula for each line. Because of this, you may want to make a "template" doc that you copy when you need a new document.
For more information on how to use the advanced conditional formatting features, take a look at this help article.
