Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a sheet that I would like to highlight cells that are equal to any of the values listed in range of cells on another sheet. How do I do this?

Sheet 1 contains values I want to highlight in green any cell that equals any value in Row A of sheet 2.

For example, column A is


I want to highlight any cell on sheet 1 that equals Apples, Oranges, or Peaches.

share|improve this question
up vote 1 down vote accepted

In Excel 2010 you can use cells from other sheets in the conditional formats formula. In earlier versions this is not possible.

To search if the value is in a list use the CountIF function.

Conditional Format Rule Dialog Box

share|improve this answer
I don't want to count the number of occurrences. I have edited my question to make it more clear. (I hope) – Bob's sock puppet Aug 9 '12 at 2:06
Have you tried it? The conditional format is asking a true false question. Anything other than 0 is true. If the count is greater than zero it will correctly highlight the cell – wbeard52 Aug 9 '12 at 2:20
Yes and it said I had too few arguments. Can you break down what's in the parenthesis? – Bob's sock puppet Aug 9 '12 at 2:25
Here is what I have =COUNTIF('Classes I Teach'!$A:$A,) what do I put after the comma for the range? – Bob's sock puppet Aug 9 '12 at 2:51
The CountIf function needs two arguments. ([Range to search in], [Value to Search]). For you it looks you need to add the value you are searching for after the 'Classes I Teach'!$A:$A. My advice, get the formula to work in an adjacent cell (not in the conditional format box) and then copy that formula to the conditional format – wbeard52 Aug 9 '12 at 3:27

Your Answer


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.