Values w/conditional formatting

[Fred Timmons]

I am managing a do-not-call list with a large contact
database. How do I establish a poitive/negative match
from source data.
eg. Cell D7 value is: 212-555-1212.
The source worksheet has a list of all phone numbers in
Column A.
So, if the value of Cell D7 matches a value in source
worksheet Column A, I can conditionally format it.
Thanks in advance.

[Dave R.]

You can use the FORMULA IS option under conditional formatting. Use a
formula like

=COUNTIF($A$1:$A$999,B1)>0

if you're referencing another worksheet, you must use indirect

=COUNTIF(INDIRECT("Sheet1!$A$1:$A$999"),B1)>0



"Fred Timmons" <[email protected]> wrote in message
news:[email protected]...
> I am managing a do-not-call list with a large contact
> database. How do I establish a poitive/negative match
> from source data.
> eg. Cell D7 value is: 212-555-1212.
> The source worksheet has a list of all phone numbers in
> Column A.
> So, if the value of Cell D7 matches a value in source
> worksheet Column A, I can conditionally format it.
> Thanks in advance.

[Bob Phillips]

Use conditional formatting with a formula of
=ISNUMBER(MATCH($D$7,$A:$A,0))

and select a colour for the format.

If that source sheet is a different sheet to D7, then you will need to give
the cells in column A a workbook name and refer to that in the formula.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fred Timmons" <[email protected]> wrote in message
news:[email protected]...
> I am managing a do-not-call list with a large contact
> database. How do I establish a poitive/negative match
> from source data.
> eg. Cell D7 value is: 212-555-1212.
> The source worksheet has a list of all phone numbers in
> Column A.
> So, if the value of Cell D7 matches a value in source
> worksheet Column A, I can conditionally format it.
> Thanks in advance.

[Aladin Akyurek]

Just

=MATCH($D$7,$A:$A,0)

should suffice.

Bob Phillips wrote:
> Use conditional formatting with a formula of
> =ISNUMBER(MATCH($D$7,$A:$A,0))
>
> and select a colour for the format.
>
> If that source sheet is a different sheet to D7, then you will need to give
> the cells in column A a workbook name and refer to that in the formula.
>
>

[Bob Phillips]

I agree, but that depends upon an error always resolving as False. That is
something that doesn't seem right to me, and so I prefer not to rely upon
it.

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Just
>
> =MATCH($D$7,$A:$A,0)
>
> should suffice.
>
> Bob Phillips wrote:
> > Use conditional formatting with a formula of
> > =ISNUMBER(MATCH($D$7,$A:$A,0))
> >
> > and select a colour for the format.
> >
> > If that source sheet is a different sheet to D7, then you will need to
give
> > the cells in column A a workbook name and refer to that in the formula.
> >
> >

[John Smith]

Thank you. How would I repeat this conditional format for
a series of cells (D7 to D500)?

>-----Original Message-----
>Use conditional formatting with a formula of
>=ISNUMBER(MATCH($D$7,$A:$A,0))
>
>and select a colour for the format.
>
>If that source sheet is a different sheet to D7, then
you will need to give
>the cells in column A a workbook name and refer to that
in the formula.
>
>
>--
>
>HTH
>
>RP
>(remove nothere from the email address if mailing direct)
>
>
>"Fred Timmons" <[email protected]>
wrote in message
>news:[email protected]...
>> I am managing a do-not-call list with a large contact
>> database. How do I establish a poitive/negative match
>> from source data.
>> eg. Cell D7 value is: 212-555-1212.
>> The source worksheet has a list of all phone numbers in
>> Column A.
>> So, if the value of Cell D7 matches a value in source
>> worksheet Column A, I can conditionally format it.
>> Thanks in advance.
>
>
>.
>

[John Smith]

Thank you. How would I repeat this conditional format for
a series of cells (D7 to D500)?


>-----Original Message-----
>I agree, but that depends upon an error always resolving
as False. That is
>something that doesn't seem right to me, and so I prefer
not to rely upon
>it.
>
>"Aladin Akyurek" <[email protected]> wrote in message
>news:[email protected]...
>> Just
>>
>> =MATCH($D$7,$A:$A,0)
>>
>> should suffice.
>>
>> Bob Phillips wrote:
>> > Use conditional formatting with a formula of
>> > =ISNUMBER(MATCH($D$7,$A:$A,0))
>> >
>> > and select a colour for the format.
>> >
>> > If that source sheet is a different sheet to D7,
then you will need to
>give
>> > the cells in column A a workbook name and refer to
that in the formula.
>> >
>> >
>
>
>.
>

[Bob Phillips]

Select all the cells D7:D500, and then go into CF and use a formula of

=ISNUMBER(MATCH($D7,$A:$A,0))

i.e. row relative

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Smith" <[email protected]> wrote in message
news:[email protected]...
> Thank you. How would I repeat this conditional format for
> a series of cells (D7 to D500)?
>
> >-----Original Message-----
> >Use conditional formatting with a formula of
> >=ISNUMBER(MATCH($D$7,$A:$A,0))
> >
> >and select a colour for the format.
> >
> >If that source sheet is a different sheet to D7, then
> you will need to give
> >the cells in column A a workbook name and refer to that
> in the formula.
> >
> >
> >--
> >
> >HTH
> >
> >RP
> >(remove nothere from the email address if mailing direct)
> >
> >
> >"Fred Timmons" <[email protected]>
> wrote in message
> >news:[email protected]...
> >> I am managing a do-not-call list with a large contact
> >> database. How do I establish a poitive/negative match
> >> from source data.
> >> eg. Cell D7 value is: 212-555-1212.
> >> The source worksheet has a list of all phone numbers in
> >> Column A.
> >> So, if the value of Cell D7 matches a value in source
> >> worksheet Column A, I can conditionally format it.
> >> Thanks in advance.
> >
> >
> >.
> >