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

Posted by ScottSEA on Super User See other posts from Super User or by ScottSEA
Published on 2011-03-15T04:32:19Z Indexed on 2011/03/15 8:12 UTC
Read the original article Hit count: 310

Filed under:

excel-2010

|

highlighting

|

conditional-formatting

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.

© Super User or respective owner

Related posts about excel-2010

Sum and chart in excel

as seen on Super User - Search for 'Super User'
I have data like the following: Both Hour and Count are columns in an excel file. Hour Count 17 79 18 122 19 123 20 142 21 150 22 78 23 15 13 33 14 33 15 40 16 33 17 56 18 46 19 35 20 67 21 65 22 45 23 36 What I want is to create a chart that shows over… >>> More
Offset the tickmarks of a secondary axis

as seen on Super User - Search for 'Super User'
I would like to offset the tickmarks of a secondary axis in excel WITHOUT changing the minimum. Here is a very complicated VBA class that apparently solves the issue but I would like a solution that doesn't require me to construct my entire chart in VBA. Here is an example of what I mean (I removed… >>> More
Find a non-case-sensitive text string within a range of cells

as seen on Super User - Search for 'Super User'
I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's the situation (simplified just a bit, for the purpose of this question): I have data in columns A-E… >>> More
How can I split Excel data from one row into multiple rows

as seen on Super User - Search for 'Super User'
Good afternoon, Is there a way to split data from one row and store to separate rows? I have a large file that contains scheduling information and I'm trying to develop a list that comprises each combination of course, day, term and period per line. For example I have a file similiar to this: Crs:Sn… >>> More
Keyboard shortcut in excel for expanding the pivot table row?

as seen on Super User - Search for 'Super User'
I'm able to expand/collapse the row in the pivot table with mouse by: right clicking on cell in the first column of the row + selecting "Expand/Collapse"/"Expand" from context menu. or double clicking on the cell in the first column of the row Is there a keybord shortcut for same action? I'm using… >>> More

Related posts about highlighting

Implementing Cell Highlighting in Rich Tables

as seen on Oracle Technology Network - Search for 'Oracle Technology Network'
A 2-part technical article >>> More
Is there such a thing as an "elisp bundle" for TextMate?

as seen on Stack Overflow - Search for 'Stack Overflow'
I started using Code Collector Pro to organise and save my Emacs codes, and this software requires TextMate bundles for syntax highlighting. They have a lisp bundle, but not an elisp bundle, at least not that I can see. I would think that the syntax highlighting would work under the lisp bundle, but… >>> More
Syntax Recognition for XML-Based Languages in Oracle JDeveloper

as seen on Oracle Blogs - Search for 'Oracle Blogs'
@Thanks Jeffrey Stephenson If you are looking at using any one of the new XML Based languages, lets say a docbook xml, or xproc, or what not, you can make use of JDeveloper's syntax highlighting and completion insight feature to ease out those extra keystrokes. All you need is a URL/local copy… >>> More
VIM does not detect syntax of .ssh/config

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
On a plain Ubuntu installation (12.04 in my case) when I have no ~/.vimrc VIM does not detect syntax of .ssh/config. Syntax highlighting works, but it does not set the correct filetype. vi ~/.ssh/config :set syn? >syntax=conf When I do: set syn=sshconfig Then the syntax highlighting… >>> More
Getting less to stop highlighting non-ascii characters

as seen on Super User - Search for 'Super User'
I have some logs which I want to read in less. Unfortunately, scattered throughout the otherwise text file are some non-ascii characters that less displays as highlighted junk. This is really annoying. How do I get rid of the highlighting? >>> More