Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting of Cells with Multiple Cell Values | Excel Discussion (Misc queries) | |||
conditional formatting consecutive cells | Excel Worksheet Functions | |||
Conditional Formatting for multiple values | Excel Discussion (Misc queries) | |||
Conditional formatting on Consecutive cells | Excel Worksheet Functions | |||
Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions |