I have a changing list of numbers on a tab that I would like to apply conditional formatting to, if the cell is present in another list of numbers, on a different sheet.

Potential Cities/Zip-Codes

List of Blocked Zip Codes

I would like the zip codes on the main "Potential Cities" sheet, to be formatted when they are listed on the "Blocked Zip Codes" sheet.

The purpose is to create a formatting change that will very plainly show the user if a zip code they are trying to enter is blocked (or on the list). Normal conditional formatting does not work because copy/paste will overwrite CF rules. I also need to be able to apply the solution to multiple different sheets, that are all checking their cells against the list of blocked cells.

CodePudding user response：

You can create an installable onEdit() trigger for your Potential Cities Spreadsheet which checks the Blocked Zips sheet for a match and applies some kind of formatting accordingly.

For example:

function checkForBlockedZips(e) {
  // do nothing if not column D
  if (e.range.getColumn() !== 4) return

  // get list of zips from blocked zips sheet
  const blockedZipsSsId = "1T9BbifVJjVMqH5iaOvxZjgmZ24ByKsft9nfUGhbDQls"
  const blockedZipsSs = SpreadsheetApp.openById(blockedZipsSsId)

  const blockedZipsSheet = blockedZipsSs.getSheetByName("Sheet1")
  const zipCodes = blockedZipsSheet.getRange("A2:A").getValues()
    .flat(2)
    .filter(x => x)

  // check if the entered value is in the list of blocked zips
  if (~zipCodes.indexOf(e.range.getValue())) {
    // create cell style
    const strikethrough = SpreadsheetApp.newTextStyle()
      .setStrikethrough(true)
      .build()
  
    const richText = SpreadsheetApp.newRichTextValue()
      .setText(e.range.getValue())
      .setTextStyle(strikethrough)
      .build()
    
    // set the cell to have the desired rich text style
    e.range.setRichTextValue(richText).setBackground("yellow")
  }
  else {
    // if the value is not a blocked zip then reset the cell style 
    const nostrikethrough = SpreadsheetApp.newTextStyle()
      .setStrikethrough(false)
      .build()

    const richText = SpreadsheetApp.newRichTextValue()
      .setText(e.range.getValue())
      .setTextStyle(nostrikethrough)
      .build()

    e.range.setRichTextValue(richText).setBackground("white")
  }
}

Things to note:

• You need to use e.range.getValue() instead of e.value so that copy/pasted values can be read
• You need to add this script to the Potential cities sheet and authorise it as an installable trigger