Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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

enter image description here

Sheet2

enter image description here

Rule #1

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

enter image description here

=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:

enter image description here

=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:

enter image description here

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

share|improve this answer
    
Clever use of Match(). But a warning though: MATCH does not distinguish between uppercase and lowercase letters when matching text values. –  Doktoro Reichard Aug 21 '13 at 13:51
    
@DoktoroReichard My assumption is that SHOES was bolded to emphasize that it is different (not included in the list), not that he actually wants to differentiate between Shoes and SHOES. If he does, it would be a bunch more complicated (you would either have to use array formulas, or if capitalization for 'proper' entries is consistent, use an additional test to make sure it follows the capitalization rules, etc.) –  jmac Aug 22 '13 at 0:06
add comment

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:

The tables

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.

How to access Conditional Formatting

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:

Part of the Conditional Formatting window

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:

Final window

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:

The end result

share|improve this answer
    
Thanks for the thorough answer. The formula only takes into account an explicit number of rows in the first sheet, where the number is indeterminate. I'll update my question to make this more clear. Also this doesn't account for non-formatting of empty cells, but that may be an easy adaptation. –  k3davis Aug 19 '13 at 12:39
    
Took care of that –  Doktoro Reichard Aug 19 '13 at 12:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.