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.
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.
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.
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 = "your-spreadsheet-id"
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:
e.range.getValue() instead of e.value so that copy/pasted values can be read