Highlighting matching cells in two different worksheets

[nichols-dale]

Worksheet 1 consists of a single column containing the first 1000
prime numbers. Worksheet 2 consists of multiple columns of numbers.
Is there a way to highlight with a chosen fill color all cells in
worksheet 2 containing prime numbers from worksheet 1? Thanks.

 

[Gary''s Student]

Use Conditional formatting with a Named range.

Say Sheet1 has the numbers in column A. Click on column A and:

Insert > Name > Define and use the Name colA.

Then click on a typical cell in Sheet2 (say B1) and:

Format > Conditional Formatting... > FormulaIs >

=COUNTIF(colA,B1)>0 and pick the color.

NOTE that without the Defined Name, conditional formatting will not work
across sheets.

 

[Sheeloo]

Can you copy Col A from Sheet1 to the sheet which you want to higlight?

If you can then assuming you copied it to Col Z do the following;
Select the range you want to highlight (assuming it starts at A1, if not
then change A1 below to the first cell)
Choose Conditional Formatting
Chooe Formula IS
Type the formula
=NOT(ISNA(VLOOKUP(A1,$Z:$Z,1,FALSE)))
Choose the format you want

 

[Shane Devenshire]

Hi,

0. Name the range containing the 1000 primes Primes
1. On the other sheet select ALL the data, for this example, I will assume
A1 is the first cell of the selection.
---
In 2003:
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:

=OR(A1=Primes)

5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:

=OR(A1=Primes)

5. Click the Format button and choose a format.
6. Click OK twice