How can I use conditional formatting to highlight cells that don't reference a cell on another sheet?

Question

I have a workbook that contains two sheets, fruits and images. The images sheet has a Fruit Reference column to specify which Fruit ID on the fruits sheet is shown in the image.

fruits sheet:

images sheet:

Now, I want to apply conditional formatting to the Fruit ID column and highlight in red all fruit IDs that do not have a matching value in the Fruit Reference column of the images sheet.

In this example, that means I want to highlight in red fruit-banana.

I have tried to use conditional formatting for this (Rule option: Use a formula to determine which cells to format), but I'm not very familiar with Excel, and I can't figure out how to write a formula that says, "for the current cell, return false if it the value doesn't match any of the values in the Fruit Reference column.)

Answer 1

Based on the hint by @cybernetic.nomad to use COUNTIF, I configured it as follows:

• On the fruits sheet, open Conditional Formatting -> Manage Rules.
• New Rule
• Use a formula to determine which cells to format
• =IF(COUNTIF(images!$B:$B, $B2) < 1, 1, 0). The IF is necessary because we need to invert the result of COUNTIF (true if there are no results, false if there are any results).
• Set formatting to fill with red.