Conditional Formatting
 

How do I change the color of a cell to green if the cell contains the phrase
"TBN" anywhere within the text
--
JPS

Conditional Formatting
 

Say for cell A1, use Formula Is:

=LEN(SUBSTITUTE(A1,"TBN",""))<LEN(A1)

--
Gary''s Student - gsnu200774


"JPS" wrote:

How do I change the color of a cell to green if the cell contains the phrase
"TBN" anywhere within the text
--
JPS

Conditional Formatting
 

If TBN is found anywhere within the text of a cell, I need the color of the
font for all of the text in that cell to be green.
--
JPS


"Gary''s Student" wrote:

Say for cell A1, use Formula Is:

=LEN(SUBSTITUTE(A1,"TBN",""))<LEN(A1)

--
Gary''s Student - gsnu200774


"JPS" wrote:

How do I change the color of a cell to green if the cell contains the phrase
"TBN" anywhere within the text
--
JPS

Conditional Formatting
 

Gary's formula in CF will do that.
If you want also to deal with "tbn" in lower case, change his CF formula to
=LEN(SUBSTITUTE(UPPER(A1),"TBN",""))<LEN(A1)
--
David Biddulph

"JPS" wrote in message
...
If TBN is found anywhere within the text of a cell, I need the color of
the
font for all of the text in that cell to be green.

"Gary''s Student" wrote:

Say for cell A1, use Formula Is:

=LEN(SUBSTITUTE(A1,"TBN",""))<LEN(A1)

"JPS" wrote:

How do I change the color of a cell to green if the cell contains the
phrase
"TBN" anywhere within the text

Conditional Formatting
 

David,
I actually need the formula to update column D, does the formula need to
cahnge any?
--
JPS


"David Biddulph" wrote:

Gary's formula in CF will do that.
If you want also to deal with "tbn" in lower case, change his CF formula to
=LEN(SUBSTITUTE(UPPER(A1),"TBN",""))<LEN(A1)
--
David Biddulph

"JPS" wrote in message
...
If TBN is found anywhere within the text of a cell, I need the color of
the
font for all of the text in that cell to be green.

"Gary''s Student" wrote:

Say for cell A1, use Formula Is:

=LEN(SUBSTITUTE(A1,"TBN",""))<LEN(A1)

"JPS" wrote:

How do I change the color of a cell to green if the cell contains the
phrase
"TBN" anywhere within the text

Conditional Formatting
 

Put the formula in the Conditional Formatting conditions for whichever
column you want the formatting to occur. In the formula, refer to the cell
whose contents you want to affect the formating.
You can use absolute addressing (the $ prefix) if you wish to apply a format
across a range of columns without it changing the reference. So, for
example, if you wanted columns D to G to be affected by the content of
column B, you could use the CF formula
=LEN(SUBSTITUTE(UPPER($B1),"TBN",""))<LEN($B1) in D1 and it would copy
unchanged across the other columns.
--
David Biddulph

"JPS" wrote in message
...
David,
I actually need the formula to update column D, does the formula need to
cahnge any?
--
JPS


"David Biddulph" wrote:

Gary's formula in CF will do that.
If you want also to deal with "tbn" in lower case, change his CF formula
to
=LEN(SUBSTITUTE(UPPER(A1),"TBN",""))<LEN(A1)
--
David Biddulph

"JPS" wrote in message
...
If TBN is found anywhere within the text of a cell, I need the color of
the
font for all of the text in that cell to be green.

"Gary''s Student" wrote:

Say for cell A1, use Formula Is:

=LEN(SUBSTITUTE(A1,"TBN",""))<LEN(A1)

"JPS" wrote:

How do I change the color of a cell to green if the cell contains
the
phrase
"TBN" anywhere within the text

Conditional Formatting
 

David,
Perfecto... Thanks for the patience. Now that I know the formula works,
please walk me through the formula so I can learn the how & why.
--
JPS


"David Biddulph" wrote:

Put the formula in the Conditional Formatting conditions for whichever
column you want the formatting to occur. In the formula, refer to the cell
whose contents you want to affect the formating.
You can use absolute addressing (the $ prefix) if you wish to apply a format
across a range of columns without it changing the reference. So, for
example, if you wanted columns D to G to be affected by the content of
column B, you could use the CF formula
=LEN(SUBSTITUTE(UPPER($B1),"TBN",""))<LEN($B1) in D1 and it would copy
unchanged across the other columns.
--
David Biddulph

"JPS" wrote in message
...
David,
I actually need the formula to update column D, does the formula need to
cahnge any?
--
JPS


"David Biddulph" wrote:

Gary's formula in CF will do that.
If you want also to deal with "tbn" in lower case, change his CF formula
to
=LEN(SUBSTITUTE(UPPER(A1),"TBN",""))<LEN(A1)
--
David Biddulph

"JPS" wrote in message
...
If TBN is found anywhere within the text of a cell, I need the color of
the
font for all of the text in that cell to be green.

"Gary''s Student" wrote:

Say for cell A1, use Formula Is:

=LEN(SUBSTITUTE(A1,"TBN",""))<LEN(A1)

"JPS" wrote:

How do I change the color of a cell to green if the cell contains
the
phrase
"TBN" anywhere within the text

Conditional Formatting
 

The functions in the formula are each standard Excel functions:
LEN
SUBSTITUTE
UPPER

For any Excel function (except DATEDIF), you will find an explanation of the
fiunction and its syntax, and usually examples and a "See Also" link to
related functions, if you type the name of the function into Excel help.
--
David Biddulph

"JPS" wrote in message
...
David,
Perfecto... Thanks for the patience. Now that I know the formula works,
please walk me through the formula so I can learn the how & why.
--
JPS


"David Biddulph" wrote:

Put the formula in the Conditional Formatting conditions for whichever
column you want the formatting to occur. In the formula, refer to the
cell
whose contents you want to affect the formating.
You can use absolute addressing (the $ prefix) if you wish to apply a
format
across a range of columns without it changing the reference. So, for
example, if you wanted columns D to G to be affected by the content of
column B, you could use the CF formula
=LEN(SUBSTITUTE(UPPER($B1),"TBN",""))<LEN($B1) in D1 and it would copy
unchanged across the other columns.
--
David Biddulph

"JPS" wrote in message
...
David,
I actually need the formula to update column D, does the formula need
to
cahnge any?
--
JPS


"David Biddulph" wrote:

Gary's formula in CF will do that.
If you want also to deal with "tbn" in lower case, change his CF
formula
to
=LEN(SUBSTITUTE(UPPER(A1),"TBN",""))<LEN(A1)
--
David Biddulph

"JPS" wrote in message
...
If TBN is found anywhere within the text of a cell, I need the color
of
the
font for all of the text in that cell to be green.

"Gary''s Student" wrote:

Say for cell A1, use Formula Is:

=LEN(SUBSTITUTE(A1,"TBN",""))<LEN(A1)

"JPS" wrote:

How do I change the color of a cell to green if the cell contains
the
phrase
"TBN" anywhere within the text