microsoft excel - Conditionally format cells based on match in another sheet

I have an Excel spreadsheet with 2 worksheets. The first is just a header row and a single column of item names. The second is a list of item groups, with a header row and a title in the left most column, with each subsequent row being one item or another from the other sheet:

Sheet1:            Sheet2:
+-------+--+--+    +-------+-------+-------+-------+-------+
| Item  |  |  |    | Group | Item1 | Item2 | Item3 | ...
+-------+--+--+    +-------+-------+-------+-------+-------+
| Shirt |  |  |    | A     | Shirt | Hat   | Tie   |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Hat   |  |  |    | B     | Socks | Shirt | SHOES |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Socks |  |  |    | C     | Hat   | Socks |       |
+-------+--+--+    +-------+-------+-------+-------+-------+
| Tie   |  |  |    | D     | Tie   | Tie   | Socks |
+-------+--+--+    +-------+-------+-------+-------+-------+
| ...   |  |  |
+-------+--+--+

I'd like to conditionally format all the cells in "Sheet2" such that any value that does not match a value in the first column of "Sheet1" is marked with a red background; those that do are marked with a green background. So all the cells in this example starting at B2 would be green except the value "SHOES". The value beneath that has nothing entered so would not be formatted at all.

The formatting rule for green I've tried is:

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)

For red, about the same:

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)

Both rules are "applied to" somewhat arbitrary range (I'd like it to apply to the whole sheet, less the topmost and leftmost row/col):

=$C$3:$E$10,$C$36:$Q$50,$E$11,$C$11,$C$2,$E$2:$Q$2,$C$12:$E$35,$F$3:$Q$35

This semi-works, but the results are unpredictable. Some values highlight as I expect but only for a few rows, and others don't. Probably my ranges are out of whack somehow, but I don't use Excel nearly as much as I once did. Can anyone lend a hand?

Thanks!

Answer

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:

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.

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)))

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))

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).