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

conditional formattinghighlightingmicrosoft-excel-2010

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.

Best Answer

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)))

Related Solutions

Excel – Conditional Formatting in excel based on another column

You can do it as follows (I'll assume that the 4.9755 is in cell A1, change as applicable):

Select the range of A1 to A10.
Pull up the conditional formatting dialog and select the option that requires a formula.
Put the formula =B1>0 and pick the formatting with font as green.
Press OK and repeat for the negative values, except using =B1<0 for the formula and the font as red for the formatting.

The number formatting doesn't need conditional formatting, you can simply select the two columns and format as appropriate.

If you want to format both columns at the same time, you can select both colums and use the formula =$B1>0 for green and =$B1<0 for red:

enter image description here

Excel – How to use Excel’s conditional formatting to highlight values closer to 0 between two columns if there are both positive and negative values

It definitely is possible with a very simple formula:

Select the appropriate cells from A2 to the last column of data in column B and enter the following for the conditional formatting formula:

=ABS(A2)=MIN(ABS($A2),ABS($B2))

Note that if both values are the same absolute amount away from zero, they will both be highlighted.

edit

Best Answer

Related Solutions

Excel – Conditional Formatting in excel based on another column

Excel – How to use Excel’s conditional formatting to highlight values closer to 0 between two columns if there are both positive and negative values

Related Question