Conditional formatting with multiple, non-consecutive values??
 

Hi, I'm surprised that I can't find this matter addressed in any user guides
since I'm sure I'm not the first person to want to do this.

Okay, I'm sure it's easy and I'll feel dumb when someone explains it: I
want to do conditional formatting for multiple, non-consecutive values. For
instance, I want to conditionally format any cells that contain the numbers
4, 8, 13 and 25. How do I do that? Excel alwys seems to want to only
conditionally format a range or a top percentile or things that fit in
formulas.

And, once I do this, how can I make this an easy function each time?
Specifically, I receive Excel tables from time to time with mailing lists. I
don't want to send mail to certain zip codes. So, I want to be able to
conditionally format the new excel tabls I receive against a standard list of
zip codes I want to exclude. I guess ultimately, I'd like to make a macro.

Thanks for any help anyone can provide.

Conditional formatting with multiple, non-consecutive values??
 

This would work:

=Or(a1=4,A1=8,A1=13,A1=25)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" <David in wrote in message
...
Hi, I'm surprised that I can't find this matter addressed in any user guides
since I'm sure I'm not the first person to want to do this.

Okay, I'm sure it's easy and I'll feel dumb when someone explains it: I
want to do conditional formatting for multiple, non-consecutive values.
For
instance, I want to conditionally format any cells that contain the numbers
4, 8, 13 and 25. How do I do that? Excel alwys seems to want to only
conditionally format a range or a top percentile or things that fit in
formulas.

And, once I do this, how can I make this an easy function each time?
Specifically, I receive Excel tables from time to time with mailing lists.
I
don't want to send mail to certain zip codes. So, I want to be able to
conditionally format the new excel tabls I receive against a standard list
of
zip codes I want to exclude. I guess ultimately, I'd like to make a macro.

Thanks for any help anyone can provide.

Conditional formatting with multiple, non-consecutive values??
 

RagDyer,

I really appreciate the fast reply. I gave it a try and nothing happened.
I went to Conditional formatting and chose highlight when equal to, then I
entered your formula. However, I used the letter v since my zip codes are in
column v. I used your exact formula except that I used several zip codes as
a test. Nothing happened. Your thoughts? What did I do wrong? Thanks
again for your time!


"RagDyeR" wrote:

This would work:

=Or(a1=4,A1=8,A1=13,A1=25)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" <David in wrote in message
...
Hi, I'm surprised that I can't find this matter addressed in any user guides
since I'm sure I'm not the first person to want to do this.

Okay, I'm sure it's easy and I'll feel dumb when someone explains it: I
want to do conditional formatting for multiple, non-consecutive values.
For
instance, I want to conditionally format any cells that contain the numbers
4, 8, 13 and 25. How do I do that? Excel alwys seems to want to only
conditionally format a range or a top percentile or things that fit in
formulas.

And, once I do this, how can I make this an easy function each time?
Specifically, I receive Excel tables from time to time with mailing lists.
I
don't want to send mail to certain zip codes. So, I want to be able to
conditionally format the new excel tabls I receive against a standard list
of
zip codes I want to exclude. I guess ultimately, I'd like to make a macro.

Thanks for any help anyone can provide.

Conditional formatting with multiple, non-consecutive values??
 

Perhaps in CF you used "Cell Value Is" when you should have used "Formula
Is" ?
--
David Biddulph

"David in VB" wrote in message
...
RagDyer,

I really appreciate the fast reply. I gave it a try and nothing happened.
I went to Conditional formatting and chose highlight when equal to, then I
entered your formula. However, I used the letter v since my zip codes are
in
column v. I used your exact formula except that I used several zip codes
as
a test. Nothing happened. Your thoughts? What did I do wrong? Thanks
again for your time!


"RagDyeR" wrote:

This would work:

=Or(a1=4,A1=8,A1=13,A1=25)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" <David in wrote in message
...
Hi, I'm surprised that I can't find this matter addressed in any user
guides
since I'm sure I'm not the first person to want to do this.

Okay, I'm sure it's easy and I'll feel dumb when someone explains it: I
want to do conditional formatting for multiple, non-consecutive values.
For
instance, I want to conditionally format any cells that contain the
numbers
4, 8, 13 and 25. How do I do that? Excel alwys seems to want to only
conditionally format a range or a top percentile or things that fit in
formulas.

And, once I do this, how can I make this an easy function each time?
Specifically, I receive Excel tables from time to time with mailing
lists.
I
don't want to send mail to certain zip codes. So, I want to be able to
conditionally format the new excel tabls I receive against a standard
list
of
zip codes I want to exclude. I guess ultimately, I'd like to make a
macro.

Thanks for any help anyone can provide.

Conditional formatting with multiple, non-consecutive values??
 

That "highlight when equal to" option is unfamiliar to me.

Are you maybe using the XL07 version?

I don't have XL07, but it should be similar enough to older versions in this
case.

Do you have an option of,
"Formula is"
in the left window?

If so, use that, then enter the formula in the next box.

Now, you say you're using Zip codes, which are probably text values.

Try enclosing them in double quotes:

=Or(v1="92552",v1="11417",v1=52891")

Leaving the cell reference "relative" (V1), as opposed to "absolute" ($V$1),
allows you to copy the format down the column so that it can apply to other
rows.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" wrote in message
...
RagDyer,

I really appreciate the fast reply. I gave it a try and nothing happened.
I went to Conditional formatting and chose highlight when equal to, then I
entered your formula. However, I used the letter v since my zip codes are
in
column v. I used your exact formula except that I used several zip codes as
a test. Nothing happened. Your thoughts? What did I do wrong? Thanks
again for your time!


"RagDyeR" wrote:

This would work:

=Or(a1=4,A1=8,A1=13,A1=25)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" <David in wrote in message
...
Hi, I'm surprised that I can't find this matter addressed in any user
guides
since I'm sure I'm not the first person to want to do this.

Okay, I'm sure it's easy and I'll feel dumb when someone explains it: I
want to do conditional formatting for multiple, non-consecutive values.
For
instance, I want to conditionally format any cells that contain the
numbers
4, 8, 13 and 25. How do I do that? Excel alwys seems to want to only
conditionally format a range or a top percentile or things that fit in
formulas.

And, once I do this, how can I make this an easy function each time?
Specifically, I receive Excel tables from time to time with mailing lists.
I
don't want to send mail to certain zip codes. So, I want to be able to
conditionally format the new excel tabls I receive against a standard list
of
zip codes I want to exclude. I guess ultimately, I'd like to make a
macro.

Thanks for any help anyone can provide.

Conditional formatting with multiple, non-consecutive values??
 

RagDyer and David B,

Yep, I have Excel 2007. I'm pretty sure I was able to choose "formula is"
but still no dice. I went to "format values where this formula is true" then
entered the formula you provided. I tried the double quotes, too. The zip
codes aren't text, they're simply 5-digit numbers.

Very frustrating. Any insight would be appreciated.

"RagDyeR" wrote:

That "highlight when equal to" option is unfamiliar to me.

Are you maybe using the XL07 version?

I don't have XL07, but it should be similar enough to older versions in this
case.

Do you have an option of,
"Formula is"
in the left window?

If so, use that, then enter the formula in the next box.

Now, you say you're using Zip codes, which are probably text values.

Try enclosing them in double quotes:

=Or(v1="92552",v1="11417",v1=52891")

Leaving the cell reference "relative" (V1), as opposed to "absolute" ($V$1),
allows you to copy the format down the column so that it can apply to other
rows.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" wrote in message
...
RagDyer,

I really appreciate the fast reply. I gave it a try and nothing happened.
I went to Conditional formatting and chose highlight when equal to, then I
entered your formula. However, I used the letter v since my zip codes are
in
column v. I used your exact formula except that I used several zip codes as
a test. Nothing happened. Your thoughts? What did I do wrong? Thanks
again for your time!


"RagDyeR" wrote:

This would work:

=Or(a1=4,A1=8,A1=13,A1=25)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" <David in wrote in message
...
Hi, I'm surprised that I can't find this matter addressed in any user
guides
since I'm sure I'm not the first person to want to do this.

Okay, I'm sure it's easy and I'll feel dumb when someone explains it: I
want to do conditional formatting for multiple, non-consecutive values.
For
instance, I want to conditionally format any cells that contain the
numbers
4, 8, 13 and 25. How do I do that? Excel alwys seems to want to only
conditionally format a range or a top percentile or things that fit in
formulas.

And, once I do this, how can I make this an easy function each time?
Specifically, I receive Excel tables from time to time with mailing lists.
I
don't want to send mail to certain zip codes. So, I want to be able to
conditionally format the new excel tabls I receive against a standard list
of
zip codes I want to exclude. I guess ultimately, I'd like to make a
macro.

Thanks for any help anyone can provide.

Conditional formatting with multiple, non-consecutive values??
 

I can't believe that XL07 is *that* different from earlier versions in this
feature.

I believe that the problem probably has to do with "data identification",
meaning ... what you have actually in the cell and what you're referencing
in the formula is different.

Whenever you're dealing with imported data, you can never be sure as to what
you actually have, datawise.

Try some of the regular steps to try to "label ' the imported data.

Say you have one of these imported values in A1.

Try:
=Isnumber(A1)
=Istext(A1)
=Len(A1)

That last test should match your visual observation.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"David in VB" wrote in message
...
RagDyer and David B,

Yep, I have Excel 2007. I'm pretty sure I was able to choose "formula is"
but still no dice. I went to "format values where this formula is true"
then
entered the formula you provided. I tried the double quotes, too. The
zip
codes aren't text, they're simply 5-digit numbers.

Very frustrating. Any insight would be appreciated.

"RagDyeR" wrote:

That "highlight when equal to" option is unfamiliar to me.

Are you maybe using the XL07 version?

I don't have XL07, but it should be similar enough to older versions in
this
case.

Do you have an option of,
"Formula is"
in the left window?

If so, use that, then enter the formula in the next box.

Now, you say you're using Zip codes, which are probably text values.

Try enclosing them in double quotes:

=Or(v1="92552",v1="11417",v1=52891")

Leaving the cell reference "relative" (V1), as opposed to "absolute"
($V$1),
allows you to copy the format down the column so that it can apply to
other
rows.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" wrote in message
...
RagDyer,

I really appreciate the fast reply. I gave it a try and nothing happened.
I went to Conditional formatting and chose highlight when equal to, then I
entered your formula. However, I used the letter v since my zip codes are
in
column v. I used your exact formula except that I used several zip codes
as
a test. Nothing happened. Your thoughts? What did I do wrong? Thanks
again for your time!


"RagDyeR" wrote:

This would work:

=Or(a1=4,A1=8,A1=13,A1=25)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" <David in wrote in message
...
Hi, I'm surprised that I can't find this matter addressed in any user
guides
since I'm sure I'm not the first person to want to do this.

Okay, I'm sure it's easy and I'll feel dumb when someone explains it: I
want to do conditional formatting for multiple, non-consecutive values.
For
instance, I want to conditionally format any cells that contain the
numbers
4, 8, 13 and 25. How do I do that? Excel alwys seems to want to only
conditionally format a range or a top percentile or things that fit in
formulas.

And, once I do this, how can I make this an easy function each time?
Specifically, I receive Excel tables from time to time with mailing
lists.
I
don't want to send mail to certain zip codes. So, I want to be able to
conditionally format the new excel tabls I receive against a standard
list
of
zip codes I want to exclude. I guess ultimately, I'd like to make a
macro.

Thanks for any help anyone can provide.

Conditional formatting with multiple, non-consecutive values??
 

RagDyeR's formula works for me in Excel 2007 as it does in earlier versions.
If you've tried with or without quotes and it still doesn't work I suggest
that your zip codes have additional characters.

Another way to format for multiple values is to list those values somewhere
on the worksheet, e.g. in H1:H10 then use this formula

=MATCH(V1,$H$1:$H$10,0)

"RagDyeR" wrote:

I can't believe that XL07 is *that* different from earlier versions in this
feature.

I believe that the problem probably has to do with "data identification",
meaning ... what you have actually in the cell and what you're referencing
in the formula is different.

Whenever you're dealing with imported data, you can never be sure as to what
you actually have, datawise.

Try some of the regular steps to try to "label ' the imported data.

Say you have one of these imported values in A1.

Try:
=Isnumber(A1)
=Istext(A1)
=Len(A1)

That last test should match your visual observation.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"David in VB" wrote in message
...
RagDyer and David B,

Yep, I have Excel 2007. I'm pretty sure I was able to choose "formula is"
but still no dice. I went to "format values where this formula is true"
then
entered the formula you provided. I tried the double quotes, too. The
zip
codes aren't text, they're simply 5-digit numbers.

Very frustrating. Any insight would be appreciated.

"RagDyeR" wrote:

That "highlight when equal to" option is unfamiliar to me.

Are you maybe using the XL07 version?

I don't have XL07, but it should be similar enough to older versions in
this
case.

Do you have an option of,
"Formula is"
in the left window?

If so, use that, then enter the formula in the next box.

Now, you say you're using Zip codes, which are probably text values.

Try enclosing them in double quotes:

=Or(v1="92552",v1="11417",v1=52891")

Leaving the cell reference "relative" (V1), as opposed to "absolute"
($V$1),
allows you to copy the format down the column so that it can apply to
other
rows.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" wrote in message
...
RagDyer,

I really appreciate the fast reply. I gave it a try and nothing happened.
I went to Conditional formatting and chose highlight when equal to, then I
entered your formula. However, I used the letter v since my zip codes are
in
column v. I used your exact formula except that I used several zip codes
as
a test. Nothing happened. Your thoughts? What did I do wrong? Thanks
again for your time!


"RagDyeR" wrote:

This would work:

=Or(a1=4,A1=8,A1=13,A1=25)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"David in VB" <David in wrote in message
...
Hi, I'm surprised that I can't find this matter addressed in any user
guides
since I'm sure I'm not the first person to want to do this.

Okay, I'm sure it's easy and I'll feel dumb when someone explains it: I
want to do conditional formatting for multiple, non-consecutive values.
For
instance, I want to conditionally format any cells that contain the
numbers
4, 8, 13 and 25. How do I do that? Excel alwys seems to want to only
conditionally format a range or a top percentile or things that fit in
formulas.

And, once I do this, how can I make this an easy function each time?
Specifically, I receive Excel tables from time to time with mailing
lists.
I
don't want to send mail to certain zip codes. So, I want to be able to
conditionally format the new excel tabls I receive against a standard
list
of
zip codes I want to exclude. I guess ultimately, I'd like to make a
macro.

Thanks for any help anyone can provide.

Conditional formatting with multiple, non-consecutive values??
 

You might try highlighting the column, then use "Edit" "Replace" to replace
any blanks with nothing.

"David in VB" wrote:

RagDyer and David B,

Yep, I have Excel 2007. I'm pretty sure I was able to choose "formula is"
but still no dice. I went to "format values where this formula is true" then
entered the formula you provided. I tried the double quotes, too. The zip
codes aren't text, they're simply 5-digit numbers.