I hv the following table:

A B C D
XT A1 C3 A2
23 C3 C3 A2
13 A2 A1 C3
XT C3 A2 C3

Is there a formula to check column A for the specific text "XT", and then
color the row (A-D) with the color of choice? What happens is that the row is
incrementally updated with data every week, hence whenever a new row has
column A with the text "XT", it should automatically convert row cells of A-D
(where "XT" resides) into a color of choice. Possible?

Use conditional formating (menu Format - conditional formating)

If the first ccell is A1 then in conditional formating chage "Cell Value Is"
to "formula Is)
1) enter the formula below
=($A1="XT")

2) Set format Pattern (conditional formating window) to the color you want.

3) copy cell A1 then select all the cells in your table and PasteSpecial
using FORMAT which will copy only the conditional format to all the cells.

"andrew" wrote:

> I hv the following table:
>
> A B C D
> XT A1 C3 A2
> 23 C3 C3 A2
> 13 A2 A1 C3
> XT C3 A2 C3
>
> Is there a formula to check column A for the specific text "XT", and then
> color the row (A-D) with the color of choice? What happens is that the row is
> incrementally updated with data every week, hence whenever a new row has
> column A with the text "XT", it should automatically convert row cells of A-D
> (where "XT" resides) into a color of choice. Possible?

Hi Joel, what if the first cell is not with the text "XT"? Does it need a
cell to refer to? See below on my actual table:

A B C D E
1/1 13 A1 C3 A2
8/1 23 C3 C3 A2
15/1 13 A2 A1 C3
22/1 XT C3 A2 C3

Column A shows the date. Column B is where i would want the formula to be
'activated' only when the text "XT" appears. When it does (in this case, B4),
it will then select row 4 from A-D and highlighted it with green (color). If
there's no "XT" appearing in the column then all is normal. In my actual
table, row 1-5 has a different data layout, then row 6 onwards is the table
you see above. Should i use UDF instead?




"Joel" wrote:

> Use conditional formating (menu Format - conditional formating)
>
> If the first ccell is A1 then in conditional formating chage "Cell Value Is"
> to "formula Is)
> 1) enter the formula below
> =($A1="XT")
>
> 2) Set format Pattern (conditional formating window) to the color you want.
>
> 3) copy cell A1 then select all the cells in your table and PasteSpecial
> using FORMAT which will copy only the conditional format to all the cells.
>
> "andrew" wrote:
>
> > I hv the following table:
> >
> > A B C D
> > XT A1 C3 A2
> > 23 C3 C3 A2
> > 13 A2 A1 C3
> > XT C3 A2 C3
> >
> > Is there a formula to check column A for the specific text "XT", and then
> > color the row (A-D) with the color of choice? What happens is that the row is
> > incrementally updated with data every week, hence whenever a new row has
> > column A with the text "XT", it should automatically convert row cells of A-D
> > (where "XT" resides) into a color of choice. Possible?

Additional question to below. Assuming if i have more than 3 different text
to monitor (each with its own color to fill) is it still possible using
Conditional Formatting? This is because the table i'm working with (per
below) has up to 5 different "text" to monitor in the same worksheet and
highlight selected cells in the row assuming if there is a match.

"andrew" wrote:

> Hi Joel, what if the first cell is not with the text "XT"? Does it need a
> cell to refer to? See below on my actual table:
>
> A B C D E
> 1/1 13 A1 C3 A2
> 8/1 23 C3 C3 A2
> 15/1 13 A2 A1 C3
> 22/1 XT C3 A2 C3
>
> Column A shows the date. Column B is where i would want the formula to be
> 'activated' only when the text "XT" appears. When it does (in this case, B4),
> it will then select row 4 from A-D and highlighted it with green (color). If
> there's no "XT" appearing in the column then all is normal. In my actual
> table, row 1-5 has a different data layout, then row 6 onwards is the table
> you see above. Should i use UDF instead?
>
>
>
>
> "Joel" wrote:
>
> > Use conditional formating (menu Format - conditional formating)
> >
> > If the first ccell is A1 then in conditional formating chage "Cell Value Is"
> > to "formula Is)
> > 1) enter the formula below
> > =($A1="XT")
> >
> > 2) Set format Pattern (conditional formating window) to the color you want.
> >
> > 3) copy cell A1 then select all the cells in your table and PasteSpecial
> > using FORMAT which will copy only the conditional format to all the cells.
> >
> > "andrew" wrote:
> >
> > > I hv the following table:
> > >
> > > A B C D
> > > XT A1 C3 A2
> > > 23 C3 C3 A2
> > > 13 A2 A1 C3
> > > XT C3 A2 C3
> > >
> > > Is there a formula to check column A for the specific text "XT", and then
> > > color the row (A-D) with the color of choice? What happens is that the row is
> > > incrementally updated with data every week, hence whenever a new row has
> > > column A with the text "XT", it should automatically convert row cells of A-D
> > > (where "XT" resides) into a color of choice. Possible?

Conditional formating can have up to 3 different formulas. The conditional
formating defaults to one formula and you can add two more using the ADD
button in the conditional formating window. Put the same formula in each of
the three ADD windows but select a different color for each formula. The
formulas wioll differ by the string "XT".

You didn't indicate which row your data was located. If XT is cell A4 then
put this formula in the conditional formating for cell A4

=($A4="XT")


"andrew" wrote:

> Additional question to below. Assuming if i have more than 3 different text
> to monitor (each with its own color to fill) is it still possible using
> Conditional Formatting? This is because the table i'm working with (per
> below) has up to 5 different "text" to monitor in the same worksheet and
> highlight selected cells in the row assuming if there is a match.
>
> "andrew" wrote:
>
> > Hi Joel, what if the first cell is not with the text "XT"? Does it need a
> > cell to refer to? See below on my actual table:
> >
> > A B C D E
> > 1/1 13 A1 C3 A2
> > 8/1 23 C3 C3 A2
> > 15/1 13 A2 A1 C3
> > 22/1 XT C3 A2 C3
> >
> > Column A shows the date. Column B is where i would want the formula to be
> > 'activated' only when the text "XT" appears. When it does (in this case, B4),
> > it will then select row 4 from A-D and highlighted it with green (color). If
> > there's no "XT" appearing in the column then all is normal. In my actual
> > table, row 1-5 has a different data layout, then row 6 onwards is the table
> > you see above. Should i use UDF instead?
> >
> >
> >
> >
> > "Joel" wrote:
> >
> > > Use conditional formating (menu Format - conditional formating)
> > >
> > > If the first ccell is A1 then in conditional formating chage "Cell Value Is"
> > > to "formula Is)
> > > 1) enter the formula below
> > > =($A1="XT")
> > >
> > > 2) Set format Pattern (conditional formating window) to the color you want.
> > >
> > > 3) copy cell A1 then select all the cells in your table and PasteSpecial
> > > using FORMAT which will copy only the conditional format to all the cells.
> > >
> > > "andrew" wrote:
> > >
> > > > I hv the following table:
> > > >
> > > > A B C D
> > > > XT A1 C3 A2
> > > > 23 C3 C3 A2
> > > > 13 A2 A1 C3
> > > > XT C3 A2 C3
> > > >
> > > > Is there a formula to check column A for the specific text "XT", and then
> > > > color the row (A-D) with the color of choice? What happens is that the row is
> > > > incrementally updated with data every week, hence whenever a new row has
> > > > column A with the text "XT", it should automatically convert row cells of A-D
> > > > (where "XT" resides) into a color of choice. Possible?