How to conditionally format a cell (in Google Sheets) if its content is present in a separate range of cells?

0
votes

I have a list of numeric codes in a range on a tab of a Google sheet. These values are non-consecutive, unsorted.

On a separate tab, I would like to conditional format a cell if its content matches any of the individual cells in the aforementioned range.

The best I can do in the "Format cells if..." dropdown is selecting "Text is exactly" and typing a single numeric code. Currently, I have a rule for each of the numeric codes. I would like to replace this collection of single rules with one rule.

1
google-sheetsgoogle-sheets-formulags-conditional-formatting

1 Answers

0
votes
=FILTER(B1, COUNTIF(INDIRECT("A$1:A"), B1))

here is an example of the opposite, so to adapt it for your purpose you need to drop NOT() and use INDIRECT() if you want to reference another sheet/tab (and you either use "text contains" or "text is exactly"):

green color:

  • apply to range A1:A custom fx: =FILTER(A1, NOT(COUNTIF(B1:E, A1)))

red color:

  • apply to range B1:B custom fx: =FILTER(B1, NOT(COUNTIF(A$1:A, B1)))
  • apply to range C1:C custom fx: =FILTER(C1, NOT(COUNTIF(A$1:A, C1)))
  • apply to range D1:D custom fx: =FILTER(D1, NOT(COUNTIF(A$1:A, D1)))
  • etc.

0