title: Format cells based on value adjacent to another cell
body: <p>I'm coaching a soccer team with 36 players and have a sheet in which I have all the players names in column A. Next to their names (in column B) I've set a ranking value from 1-5 where the best players have a 5.</p>
  
  <p>On a weekend we may have 3 games and I pick 3 teams of 10 players from the 36 players and list the players of the 3 different teams in column D, E and F.</p>
  
  <p>I'd like to format the backgrounds of the cells in columns D, E, and F with 5 different colors depending on the ranking of the player. For example if a player named John has a 5 in rating the background color should be green, if ranking is 4 then yellow and so on. </p>
  
  <p>Also if possible, I'd like to sum up the total ranking points of a team in the cell after the last team member, (we're always 10 in each team). All this in order to easily see if the teams are fairly equal.</p>
  
  <p>Would be really happy for assistance :) </p>
  
  <p>here is a link to the sheet
  <a href="https://docs.google.com/spreadsheets/d/1cm-i1OIVoxE7eQdKC3o8RtOzcxoJoLaw1esXL2yMBGA/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1cm-i1OIVoxE7eQdKC3o8RtOzcxoJoLaw1esXL2yMBGA/edit?usp=sharing</a></p>