Conditionally format cells based on match in another sheet

Question

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 1

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

Answer 2

Seems like what you need is Cell Conditional Formatting.

Here is a link describing what this is.

My pics are from a Portuguese version of Excel 2003, but the functionality should also be there on Office 2010. In fact, Office 2010 allows for conditional formatting to exist between sheets, something 2003 can't and as such, I'm doing all on one sheet.

First of, the table:

What you want to do is to compare a element from the 2nd table from all elements from the 1st. So, you need to write a function like this:

=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))

What EXACT() does is compare two strings of text. What OR() does is to become True if any logical condition inside is True.

This being said, you then select all cells from the 2nd table and then press Conditional Formatting, as shown.

Knowing about Office 2010 and it's new Ribbon interface, you should look in the Format pane for this. If I recall, it stands as an icon.

Clicking that icon will bring about a window similar to this:

In there, you first need to select that you want a formula and then you paste the formula I mentioned before. To make all cells that verify the condition green, just alter the format. To do the red formatting, just use NOT(OR(...)); this will return the inverse of the condition you set.

To make sure it doesn't format the cells that have nothing in them, make a third condition in which the formula is ISBLANK(E3) (being E3 the upper left corner).

Something I checked after writing the initial draft is precedence. At least on my version, Condition 1 is verified before Condition 2 and so forth. So, you should have the conditions ordered in a way they don't interfere with themselves. So:

1st condition - =ISBLANK(E3)
2nd condition - =OR(...)
3rd condition - =NOT(OR(...))

Therefore, you should have a window like this:

Try to adapt this to your situation. If I recall, it isn't that different from what I'm showing. The result should be something like this: