Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting matching cells in two different worksheets
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting matching cells in two different worksheets
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. -- Gary''s Student - gsnu2007L " wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting matching cells in two different worksheets
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 " wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlighting matching cells in two different worksheets
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 -- If this helps, please click the Yes button. Cheers, Shane Devenshire " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching Cells on different worksheets | Excel Discussion (Misc queries) | |||
Highlighting non-matching ZIP in a batch | Excel Discussion (Misc queries) | |||
How do I compare data in two worksheets to find matching cells? | Excel Discussion (Misc queries) | |||
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) | |||
finding matching cells in worksheets | Excel Worksheet Functions |