Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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) – Move More Comments Link To Top 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? – Move More Comments Link To Top 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? – Move More Comments Link To Top 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .