problem with function find value and set background in cell or row

[ShiizophreN]

Hi, I need your help again, I would like to enter a search term in cell B1 in a spreadsheet and then give the cell a background. Unfortunately, the function doesn't work that way for me yet

Kind Regards

function searchValueInRange() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var rows = sheet.getDataRange();

  var numRows = rows.getNumRows();

  var values = rows.getValues();

  var search = sheet.getRange(1,1);

  var searchValue = 0;

  for (var i = 0; i <= numRows - 1; i++) {

    var row = values[i];

    if (row[1] == search.getValue() || row[2] == search.getValue()) { // Durchsucht Zellen in Spalte A, wechseln Sie zu Zeile[1] für Spalte B etc. 

       search.setBackgroundRGB(255, 0, 0);

    }

  }

}

[Clark Lind]

Hello, you want to enter a search term in cell B1, and then highlight (set backgound) of any cells found? Or just change the background of B1 if the search term is present?

[ShiizophreN]

a search term in cell B1, and then highlight (set backgound) of any cells found

[Clark Lind]

Maybe something like this? 

function searchValueInRange() {
  var sheet = SpreadsheetApp.getActiveSheet();

  var dataRange = sheet.getDataRange();

  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var search = sheet.getRange(1,1);

  var searchValue = search.getValue();

  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];

    if (row[1] == searchValue) {
       row[1].setBackgroundRGB(255, 0, 0);
    }
    if (row[2] == searchValue ) {
       row[2].setBackgroundRGB(255, 0, 0);

[ShiizophreN]

Hi thanks again for your competent help, can you add a command that automatically jumps to the cell with the highlighted background?

[Clark Lind]

That will be difficult if there is more than one match. But assuming only one match then maybe the below. 
If there is more than one match, then it will stop on the last match:

function searchValueInRange() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var search = sheet.getRange(1,1);

  var searchValue = search.getValue();
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if (row[1] == searchValue) {
       row[1].setBackgroundRGB(255, 0, 0);

       row[1].activate()

    }
    if (row[2] == searchValue ) {
       row[2].setBackgroundRGB(255, 0, 0);

       row[2].activate();

    }
  }
}

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/xdBXBeNZZ8k/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8d4fbeeb-9823-4b41-9af7-305c749799d3n%40googlegroups.com.