Cell formating based on other cell value google sheets

Question

I have 2 sheets the first one is Orders and second one is the ShippingDoc.

At Shipping doc, I have Cell C2. In that cell I choose/write ID from range of A7:A from Orders.

Is there a way if I choose example ID 1 at C2 then automatically A7 at Orders to change background to green. Also if I change the ID to 2 the A8 to be green and to not delete the A7 color.

So, just to clarify, you want to be able to change the color of A7 in orders if you write ID 1 and change also the color of A8 in Orders if your ID is 2 right? — Mateo Randwolf
Yes if i write ID 1 at C2 then A7 to be green then if change id to 2 at C2 and A8 to be green also to keep the A7 green too — hits123

Mateo Randwolf Mateo Randwolf · Accepted Answer · 2020-02-19T14:14:39

There is a better way of achieving this using Apps Script. Go to tools->Script editor and there use the following code (explained on the comments in the code):

  // Update everytime the value on C2 changes
  function onEdit() {
    var spreadsheet = SpreadsheetApp.getActive();
    // Get C2 value
    var C2value = spreadsheet.getSheetByName("ShippingDoc").getRange('C2').getValue();
    
    // If input value is higher than 0
    if(C2value>0){
      // Ofset to set the background after A7
      var number = C2value+6;
      // Set background color of appropiate cell
      spreadsheet.getSheetByName("Orders").getRange('A'+number+'').setBackground('green');
    }
    
  }

Check out more information about how to do amazing things in sheets with simple scripts HERE

I hope this has helped you. Let me know if you need anything or if you did not understand something.

Cell formating based on other cell value google sheets

2 Answers