How to use a range for conditional formatting in excel 2010

Question

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

Apples
Oranges
Peaches

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

wbeard52 · Accepted Answer · 2012-08-09 01:38:07Z

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

answered Aug 9 '12 at 1:38

wbeard52
1,345617

	I don't want to count the number of occurrences. I have edited my question to make it more clear. (I hope) – Larry 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? – Larry 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? – Larry 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

1 Answer