CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsconditional-formatting
Truex
Truex

Reputation: 317

Highlight Google Sheet cell (red) when two different cell's text do not match

basically what I am trying to achieve is to highlight or set background of the cell that has a 'typo' so does not match to another one.

I have a tracker used by my team, they enter the code version in column D. Column C contains the environment name, e.g. QA, PROD.

When QA testing activities are completed, they simply copy the code version to a row below, but what happens sometimes, they manually enter the version accidentally making a mistake (typo) - therefore the code that is implement to prod will be incorrect and can cause system malfunction.

Column A will be a primary key let's call it this way (module)

I am trying to validate the text they enter in the cell so I either want to use conditional formatting or a script. Conditional formatting in my opinion will not work properly as the function is quite advance, so it will be easier to implement a script. Is the below script correct?

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getSheetByName("2022").getRange(1,1);
  var cellRange = range.getValues();

  for(i = 0; i<cellRange.length-1; i++){
     if(cellRange[i][0] == "PROD")
     elseif(cellRange[i][0] == "QA")
     {
       ss.getSheetByName("2022").getRange(i+2,6).setBackground("red");
     }
  }
}

In case I use conditional formatting it would have to be: if environment equals to PROD and QA, and if QA & PROD module is the same, CODE version should match... if does not match, highlight a prod code cell with red color....

Upvotes: 0

Views: 1549

Answers (2)

Truex
Truex

Reputation: 317

@Oriol Castander, it is pretty much as you described above:

enter image description here

As you can see, there is a discrepancy in values between TAGS (column E) which should not happen. So basically I want to prevent such thing of happening, by highlighting mismatched values with red color.

Upvotes: 0

Oriol Castander
Oriol Castander

Reputation: 648

I assume that the structure of your code is as follows:

example spreadsheet

and that your objective is to modify the background color of the C column (in this example) if the values in the A and B columns do not match.

As you said, this can either be accomplished with conditional formatting or with a script attached to the spreadsheet.

  • If you wish to do it via conditional formatting (which is what I would recommend) you need to select the C2 cell, open Format -> Conditional Formatting, and inside the Format Rules change Format cells if... to Custom formula is. Then enter the following formula:
=A2<>B2

which will trigger the C2 cell coloring whenever the values in A2 and B2 are not equal. You can now scroll down this formatting to apply to as many rows you want. You can read more about conditional formatting here.

  • If you wish to implement a script, this will do the job:
function myFunction() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadSheet.getSheetByName("Sheet1");

  var range = sheet.getRange(2,1,sheet.getLastRow()-1, 2);
  //SHOULD ADAPT THE VALUES IN THE getRange TO FIT YOUR SPREADSHEET

  var values = range.getValues();

  for (var row = 0; row < values.length; row++){

    if(values[row][0] != values[row][1]){
      sheet.getRange(row+2,3).setBackground('red');
    }
    else{
      sheet.getRange(row+2,3).setBackground('white');
    }
  }
}

where you will need to adjust your ranges to fit your spreadsheet (this works for the example spreadsheet above).

Upvotes: 2

Related Questions