Excel: Conditional Formatting (Highlighting) Values Based on Another Worksheet

I have a workbook that has two worksheets.

The first worksheet is simply a list of the first 78,498 prime numbers in a single column, A1-A78498.

The second worksheet has a grid of numbers from 1 to n.

The goal is to highlight the cells with prime numbers in the grid by referencing the prime number values in the other worksheet. Is this possible, and if so, how?

edit

I have named the column with my prime numbers "PRIMES1T".

I would like the formula to work for the entire worksheet, regardless of size, but my excel-fu is extremely weak. If at all possible, I would like to be able to enter the formula in the dialog box for conditional formatting (as below): I have tried `=NOT(ISNA(MATCH(A:Z,PRIMES1T,0)` (only A-Z, but have to start somewhere) with no luck.

-

in conditional formatting, to reference a range on another worksheet (or workbook) you need to use a `named range` Assuming you name your prime numbers range `rPrimes` then the conditional format formula would be (for cell A1)

``````=NOT(ISNA(MATCH(A1,rPrimes)))
``````
-
neilson: how would I get this to work for the entire spreadsheet, regardless of size? I tried `=NOT(ISNA(MATCH(A:Z, PRIMES1T, 0)` (not everything, I know, but have to start somewhere) to no avail. – ScottSEA Mar 15 '11 at 7:01
@ScottSEA just put the formula as written above on cell A1 then copy paste special format to all cells you want it applied to. while you could apply it to every cell in the sheet thats probably not a good idea as it would be quite intensive calculation! – chris neilsen Mar 15 '11 at 11:45
Thanks very much! – ScottSEA Mar 15 '11 at 16:14
@ScottSEA happy to help. if this is the answer you were looking for, you should accept it (click the tick) – chris neilsen Mar 15 '11 at 19:07