title: Google script to color a cell based on another cells value
body: <p>I'm trying to change the color of a cell in a column based on if it bigger or not than another cell (in the same row)</p>
  
  <p>I have set this up if conditional formatting, but formulas get messed around as I copypaste in the sheet, hence I'm looking to do the same with gs.</p>
  
  <p>I have found that I need to use <code>onEdit()</code> (<a href="https://developers.google.com/apps-script/guides/triggers/#Simple" rel="nofollow noreferrer">https://developers.google.com/apps-script/guides/triggers/#Simple</a>) to get the changes updated on every cell change.</p>
  
  <p>And got some code from another questions here, but I'm getting an error in the getRange. </p>
  
  <pre><code>function onEdit(e) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Programar');
    var values = sheet.getRange(2, 16, 3).getValues();
    var values_now = sheet.getRange(2, 5, 3).getValues();
    var color = '';  
    var current_row = 2
    for (var i = 0; i &lt; values.length; i++)
    {
  
      if (values_now[i][0] &gt; values[i][0])
      {
              SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .alert(values_now[i][0] + ' ' + values[i][0]);
        color='#FFDD88'
      }
      else if (values_now[i][0] &lt; values[i][0])
      {
        color = '#CC6666'
      }
      sheet.getRange(current_row, 5).setBackgroundColor(color);
      current_row++
    }
  
  }
  
  </code></pre>
  
  <p>The range I select in values is P2:P243 and in values_now E2:E243, this last range is the one i like to color the cell.</p>
  
  <ul>
  <li>So if P2 is Bigger than E2, E2 should be in Blue</li>
  <li>If P32 is smaller than E32, E32 should be in Red. an so on...</li>
  </ul>