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.

Answer 1

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.