Conditional Formatting - comparison with another cell
 

I'm trying to get a cell highlighted if its value contains the (alphabetic)
value in another cell. I've got a rule in cell C16 of =B5 ="td" and this
correctly highlights when C16 and C5 are both set to 'td'.

But I want it to really to be 'contains' not '=' and then to be able to put
in a string of values 'td, ps, jc' and so on. So C16 could be set to 'JC/PS'
and would go red if C5 contained either 'JC' or 'PS'.

Conditional Formatting - comparison with another cell
 

First, do note that your first formula
=B5="td"

has no correlation to what is going on in C16. It only checks if B5 = "td".

As to your question, the CF formula is:
=ISNUMBER(SEARCH(C5,C16))

Note that if you want it to be case-sensitive, change SEARCH to FIND.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jezza" wrote:

I'm trying to get a cell highlighted if its value contains the (alphabetic)
value in another cell. I've got a rule in cell C16 of =B5 ="td" and this
correctly highlights when C16 and C5 are both set to 'td'.

But I want it to really to be 'contains' not '=' and then to be able to put
in a string of values 'td, ps, jc' and so on. So C16 could be set to 'JC/PS'
and would go red if C5 contained either 'JC' or 'PS'.

Conditional Formatting - comparison with another cell
 

Try:

=IF(ISERROR(FIND("td",B5)),FALSE,TRUE)

"Jezza" wrote:

I'm trying to get a cell highlighted if its value contains the (alphabetic)
value in another cell. I've got a rule in cell C16 of =B5 ="td" and this
correctly highlights when C16 and C5 are both set to 'td'.

But I want it to really to be 'contains' not '=' and then to be able to put
in a string of values 'td, ps, jc' and so on. So C16 could be set to 'JC/PS'
and would go red if C5 contained either 'JC' or 'PS'.

Conditional Formatting - comparison with another cell
 

You don't need
=IF(...,FALSE,TRUE)

=NOT(ISERROR(FIND("td",B5))) should do the job,
or =ISNUMBER(FIND("td",B5))
or =ISNUMBER(SEARCH("td",B5)) if you want the search to be case insensitive
which the OP wanted.
--
David Biddulph

"Sean Timmons" wrote in message
...
Try:

=IF(ISERROR(FIND("td",B5)),FALSE,TRUE)

"Jezza" wrote:

I'm trying to get a cell highlighted if its value contains the
(alphabetic)
value in another cell. I've got a rule in cell C16 of =B5 ="td" and this
correctly highlights when C16 and C5 are both set to 'td'.

But I want it to really to be 'contains' not '=' and then to be able to
put
in a string of values 'td, ps, jc' and so on. So C16 could be set to
'JC/PS'
and would go red if C5 contained either 'JC' or 'PS'.

Conditional Formatting - comparison with another cell
 

Good tips. Thank you for the help!

"David Biddulph" wrote:

You don't need
=IF(...,FALSE,TRUE)

=NOT(ISERROR(FIND("td",B5))) should do the job,
or =ISNUMBER(FIND("td",B5))
or =ISNUMBER(SEARCH("td",B5)) if you want the search to be case insensitive
which the OP wanted.
--
David Biddulph

"Sean Timmons" wrote in message
...
Try:

=IF(ISERROR(FIND("td",B5)),FALSE,TRUE)

"Jezza" wrote:

I'm trying to get a cell highlighted if its value contains the
(alphabetic)
value in another cell. I've got a rule in cell C16 of =B5 ="td" and this
correctly highlights when C16 and C5 are both set to 'td'.

But I want it to really to be 'contains' not '=' and then to be able to
put
in a string of values 'td, ps, jc' and so on. So C16 could be set to
'JC/PS'
and would go red if C5 contained either 'JC' or 'PS'.


.