Conditionally format cells based on match in another sheet

As Doktoro Reichard states, you want to use Conditional Formatting to do this. In this specific case you want to have three rules:

1. If the cell is blank, do not change the background
2. If the cell has a match, make the background green
3. If the cell doesn't have a match, make the background red

_{Apologies, my Excel is Japanese. It's multilingual day.}

To do this, we need 3 formulas that will return TRUE or FALSE for each of these conditions. I will assume your data looks as follows:

Sheet1

Sheet2

Rule #1

The following formula will return whether or not the cell is blank. I have selected

=ISBLANK(B2)

Note that I have selected cells B2:D5 with relative references. This will apply the same formula changing the cell reference for every cell in the selected range. Set the background color to white (or whatever your preference is) when this condition is true.

Rule #2

The following formula will return whether or not there is a perfect match in the list on sheet 1:

=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))

Rule #3

The following formula will return whether or not there is no perfect match in the list on sheet 1:

=ISERROR(MATCH(B2,Sheet1!$A:$A,0))

Order

The rule on top will be executed first. So since all blank cells will be non-matches, you need to put the blank rule first. The order of #2 and #3 doesn't matter (they will never overlap).