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?

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?

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?

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?

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?

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?