Conditional formatting with multiple, non-consecutive values??

[David in VB]

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.

 

[RagDyeR]

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!
=====================================================

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.

 

[David in VB]

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!

 

[David Biddulph]

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

 

[RagDyeR]

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!
=====================================================

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!

 

[David in VB]

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]

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

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.

 

[daddylonglegs]

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)

 

[zoogler]

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