0

I have a spreadsheet that looks like this (but much larger):

Both the student names and the house assignments are pulled from other sheets.

I am looking for a google script that would conditionally format a student name based on the house they are assigned to. The house assignment will always be listed in the cell to the right of the name.

I realize this can be done with conditional formating (For instance: Custom formula =B3:B4="6-Blue"), but it becomes an arduous process because I have to go into each vertical range individually to make a change if the house name changes. With a google script, I could find and replace all instances of a house name.

Something like Eric Koleda's answer may work, but I am unsure how to reference the ranges and add multiple values to search for.

Community
  • 1
  • 1
Jake
  • 173
  • 1
  • 2
  • 16

1 Answers1

1

You can still do this with conditional formatting!

You can set up conditional formatting on a per column basis, you don't have to change the formatting, only the name of the color in your house column.

  • Conditional formatting formula: =ISNUMBER(FIND("Red", $C4))
  • Conditional formatting range: B4:C

This will try and find the text Red within all cells in Column C starting at C4. If Red is found in a cell, FIND() will return the index of Red, if it doe snot find Red is returns an error. You can check this with ISNUMBER(), which returns true or false if the value is a number or not.

This will color the adjacent columns on that row within the range. ie. A range of B4:C will color the cells in column C and B, a range of just B4:B will only cause the cells in column B to be colored.

You only need to setup one conditional formatting per color, per set of columns that you have. After that, just changing the colors under your houses will change the formatting.

Here is an example sheet I made for you: Example Google Sheet

Picture of Sheet with formatting: enter image description here

Douglas Gaskell
  • 9,017
  • 9
  • 71
  • 128
  • Thanks Douglas, The final document ends up with 12 columns X 3 grade levels, so there is certainly an efficiency payoff in your method as long as the house names repeat across grade levels. A script would also have have the added benefit of easily transferring the conditional formats from one sheet to another. Any ideas how to accomplish the conditional formating with a script? – Jake Apr 27 '16 at 01:31
  • You can adjust the background color of cells based on their values with a script, yes. However, you will need to make your script loop through each column of data, utilizing a regex match for the color you want. For formatting, I usually discourage the use of a script as Conditional Formatting almost always does the trick. I tend to reserve scripts for data manipulation. If you really want to make yourself a script based solution, look at this documentation for `setBackgroundColor` https://developers.google.com/apps-script/reference/spreadsheet/range#setbackgroundcolor – Douglas Gaskell Apr 27 '16 at 01:36
  • The house names don't really need to repeat. It just looks for the color in the name of the house and sets it to that. You should be able to copy/paste the conditional formatting across sheets. – Douglas Gaskell Apr 27 '16 at 01:37