Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
I'm trying to set the conditional formatting so that if a cell contains X or
Y but does not have a begining and end parenthesis, it will turn red. How do I do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Format-Conditional Formatting. Change first dropdown to "formula is". Input:
=AND(OR(ISNUMBER(SEARCH("y",A2)),ISNUMBER(SEARCH(" x",A2))),ISERROR(SEARCH("(",A2)),ISERROR(SEARCH(") ",A2))) Click Format, Pattern, select red. Hit "ok", then "ok" again. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "lightbulb" wrote: I'm trying to set the conditional formatting so that if a cell contains X or Y but does not have a begining and end parenthesis, it will turn red. How do I do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Hi,
Luke may be correct but you really need to specify the possible data layouts for example Hi Luke, I can't get that to work if the data is, for example what do you want to do with the following: ab(m)x ab(abxu) a()x abd(xy) def(mmm)x (x y) but then we don't know if the user data can look like any of the above. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "lightbulb" wrote: I'm trying to set the conditional formatting so that if a cell contains X or Y but does not have a begining and end parenthesis, it will turn red. How do I do this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Luke,
Very similar to what I just came up with. I'm just a little too slow! Here's my version (again, using the "FORMULA IS" in the dropdown): =IF(AND(ISERROR(SEARCH("X",A1,1)),ISERROR(SEARCH(" Y",A1,1))),0,IF(OR(ISERROR(SEARCH("(",A1,1)),ISERR OR(SEARCH(")",A1,1))),1,0))=1 Yours is more compact and elegant. Out of curiousity - why the switch from ISNUMBER to ISERROR? Regards, Eric "Luke M" wrote: Format-Conditional Formatting. Change first dropdown to "formula is". Input: =AND(OR(ISNUMBER(SEARCH("y",A2)),ISNUMBER(SEARCH(" x",A2))),ISERROR(SEARCH("(",A2)),ISERROR(SEARCH(") ",A2))) Click Format, Pattern, select red. Hit "ok", then "ok" again. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "lightbulb" wrote: I'm trying to set the conditional formatting so that if a cell contains X or Y but does not have a begining and end parenthesis, it will turn red. How do I do this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Hi,
If Luke's intretation of your question is correct then you could use the following formula =MAX(ISNUMBER(FIND($H$1:$H$2,A1))*ISNUMBER(FIND($I $1:$I$2,A1)))=0 In this case you enter (X) in H1 and (Y) in H2 and X and Y in I1:I2. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "lightbulb" wrote: I'm trying to set the conditional formatting so that if a cell contains X or Y but does not have a begining and end parenthesis, it will turn red. How do I do this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Thanks! When search is successful, it returns a number, when it's not, kicks
out an error. Another method that was possible: NOT(ISERROR(SEARCH("x",A2"))) Rather than use an extra arguement, I just went with the ISNUMBER. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "EricG" wrote: Luke, Very similar to what I just came up with. I'm just a little too slow! Here's my version (again, using the "FORMULA IS" in the dropdown): =IF(AND(ISERROR(SEARCH("X",A1,1)),ISERROR(SEARCH(" Y",A1,1))),0,IF(OR(ISERROR(SEARCH("(",A1,1)),ISERR OR(SEARCH(")",A1,1))),1,0))=1 Yours is more compact and elegant. Out of curiousity - why the switch from ISNUMBER to ISERROR? Regards, Eric "Luke M" wrote: Format-Conditional Formatting. Change first dropdown to "formula is". Input: =AND(OR(ISNUMBER(SEARCH("y",A2)),ISNUMBER(SEARCH(" x",A2))),ISERROR(SEARCH("(",A2)),ISERROR(SEARCH(") ",A2))) Click Format, Pattern, select red. Hit "ok", then "ok" again. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "lightbulb" wrote: I'm trying to set the conditional formatting so that if a cell contains X or Y but does not have a begining and end parenthesis, it will turn red. How do I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |