Conditional Formatting of Cells based on discontinuous range ofnumbers

  • Thread starter Thread starter mholmes71
  • Start date Start date
M

mholmes71

Hello,
I have a column of numbers that I want to match to another column.
In column C, I have numbers that have discontinuous values from 0 to
10,000. In Column N, I have a similar but smaller subsample of
numbers (again ranging from 0 to 10,000). I would like to make all of
the cells in column C that have a matching number anywhere in column
N, yellow. Is this possible? Conditional formatting does not seem to
work on ranges. Thanks in advance.

Mike
 
try this

select the range for col C , go to format | conditional format |
condition 1 : formula is =NOT(ISNA(MATCH(C1,$N$1:$N$11,0))) | then
choose the format | ok |

Change the range as u required.
 
=NOT(ISNA(MATCH(C1,$N$1:$N$11,0)))

You can reduce that to:

=MATCH(C1,$N$1:$N$11,0)

MATCH will return either a number that is greater than 0 or the error #N/A.
When it returns a number the format will be applied.

--
Biff
Microsoft Excel MVP


try this

select the range for col C , go to format | conditional format |
condition 1 : formula is =NOT(ISNA(MATCH(C1,$N$1:$N$11,0))) | then
choose the format | ok |

Change the range as u required.
 
Hi,

You can take a another approach:

1. Highlight all the cells in column C
2. Choose Format, Conditional Formatting, choose Formula is from the first
drop down and
3. Enter the following formula in the second box

=OR(C1=N$1:N$14)

4. click Format and select yellow on the Patterns tab

If this helps please click the Yes button

Cheers,
Shane Devenshire
 
Back
Top