how to highlight/alert D5 with the background color, If Cell (D5) value matches with another workbook ID range Sheet2!(A2:A). based on two criteria.

One is with a number eg.345 and another is a prefixed letter with number eg. R345

CodePudding user response：

I believe your goal is as follows.

• You want to search the values of the column "A" of "Sheet2" using a value retrieved from the cell "D5" of "Sheet1".
• For example, when the search value is R345, when 345 and R345 are found in the column "A" of "Sheet2", you want to change the background color of "D5" of "Sheet1" to the red color.
  • In this case, I thought that when the search value is not found, you might want to remove the background color.
• You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const sheetName1 = "Sheet1"; // Please set the sheet name.
  const sheetName2 = "Sheet2"; // Please set the sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [sheet1, sheet2] = [sheetName1, sheetName2].map(s => ss.getSheetByName(s));
  const searchRange = sheet1.getRange("D5");
  const searchValue = searchRange.getValue();
  const m = typeof searchValue == "string" ? Number(searchValue.match(/(\d )/)[1]) : searchValue;
  const search = sheet2.getRange("A2:A"   sheet2.getLastRow()).createTextFinder(`^R${m}$|^${m}$`).useRegularExpression(true).matchEntireCell(true).findAll();
  searchRange.setBackground(search.length == 2 ? "red" : null);
}

• In this sample script, the search value is retrieved from "D5" of "Sheet1". And, the values are searched from column "A" of "Sheet2". When 2 values are found, the background color is changed to a red color.
• I used TextFinder for checking the number of searched values.

Note:

• The above sample script can be run with the script editor. For example, how about the following sample script? In this case, when you edit the cell "D5", the script is run by the simple trigger of OnEdit.

    function onEdit(e) {
      const sheetName1 = "Sheet1"; // Please set the sheet name.
      const sheetName2 = "Sheet2"; // Please set the sheet name.
  
      const range = e.range;
      const sheet = range.getSheet();
      if (sheet.getSheetName() != sheetName1 || range.getA1Notation() != "D5") return;
      const sheet2 = e.source.getSheetByName(sheetName2);
      const searchValue = range.getValue();
      const m = typeof searchValue == "string" ? Number(searchValue.match(/(\d )/)[1]) : searchValue;
      const search = sheet2.getRange("A2:A"   sheet2.getLastRow()).createTextFinder(`^R${m}$|^${m}$`).useRegularExpression(true).matchEntireCell(true).findAll();
      range.setBackground(search.length == 2 ? "red" : null);
    }
  

Reference:

• createTextFinder(findText) of Class Range