Conditional formatting based on text

[Gilles Desjardins]

Hi everyone, I've been struggling with this next request.

Is it possible to color a row with a specific color based on finding a
specific word?
If in row 5 the word engineer shows up I would like to turn the whole row
green. No matter where in row 5 the word shows up. I've tried
=IF(5:5="engineer",MOD(ROW(),2)=1,) and this works only for a word in A5.
I can't seem to use A5:IV5
Any help would be appreciated. Gilles

[CLR]

Try putting your "condition" inside an IF statement in a helper
cell.......... making the true condition=1 and the false condition=0

Then apply your conditional formatting based on that helper cell being 1 or
0

Vaya con Dios,
Chuck, CABGx3



"Gilles Desjardins" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone, I've been struggling with this next request.
>
> Is it possible to color a row with a specific color based on finding a
> specific word?
> If in row 5 the word engineer shows up I would like to turn the whole row
> green. No matter where in row 5 the word shows up. I've tried
> =IF(5:5="engineer",MOD(ROW(),2)=1,) and this works only for a word in A5.
> I can't seem to use A5:IV5
> Any help would be appreciated. Gilles
>
>

[Biff]

Hi!

Select row 5
Conditional Formatting
Formula is: =OR(5:5="engineer")

Biff

>-----Original Message-----
>Hi everyone, I've been struggling with this next request.
>
>Is it possible to color a row with a specific color based
on finding a
>specific word?
>If in row 5 the word engineer shows up I would like to
turn the whole row
>green. No matter where in row 5 the word shows up. I've
tried
>=IF(5:5="engineer",MOD(ROW(),2)=1,) and this works only
for a word in A5.
>I can't seem to use A5:IV5
>Any help would be appreciated. Gilles
>
>
>.
>

[Ken Wright]

If you wanted the whole sheet like that then select the whole sheet and use

=COUNTIF(1:1,"*engineer*")>0

else select row 5 and use

=COUNTIF(5:5,"*engineer*")>0

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Gilles Desjardins" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone, I've been struggling with this next request.
>
> Is it possible to color a row with a specific color based on finding a
> specific word?
> If in row 5 the word engineer shows up I would like to turn the whole row
> green. No matter where in row 5 the word shows up. I've tried
> =IF(5:5="engineer",MOD(ROW(),2)=1,) and this works only for a word in A5.
> I can't seem to use A5:IV5
> Any help would be appreciated. Gilles
>
>

[Gilles Desjardins]

Thank you all for your suggestions. Biff's suggestions is the one that hits
the nail on the head.

Gilles
"Gilles Desjardins" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone, I've been struggling with this next request.
>
> Is it possible to color a row with a specific color based on finding a
> specific word?
> If in row 5 the word engineer shows up I would like to turn the whole row
> green. No matter where in row 5 the word shows up. I've tried
> =IF(5:5="engineer",MOD(ROW(),2)=1,) and this works only for a word in A5.
> I can't seem to use A5:IV5
> Any help would be appreciated. Gilles
>

[Ken Wright]

So there is no chance that the word engineer will be part of a sentence, or
have any other characters in the cell with it, it will ALWAYS be the ONLY
entry in the cell yes?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Gilles Desjardins" <[email protected]> wrote in message
news:[email protected]...
> Thank you all for your suggestions. Biff's suggestions is the one that
hits
> the nail on the head.
>
> Gilles
> "Gilles Desjardins" <[email protected]> wrote in message
> news:[email protected]...
> > Hi everyone, I've been struggling with this next request.
> >
> > Is it possible to color a row with a specific color based on finding a
> > specific word?
> > If in row 5 the word engineer shows up I would like to turn the whole
row
> > green. No matter where in row 5 the word shows up. I've tried
> > =IF(5:5="engineer",MOD(ROW(),2)=1,) and this works only for a word in
A5.
> > I can't seem to use A5:IV5
> > Any help would be appreciated. Gilles
> >
>
>

[CLR]

Now THAT is really cool Ken...............well done!

Vaya con Dios,
Chuck, CABGx3


"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> If you wanted the whole sheet like that then select the whole sheet and
use
>
> =COUNTIF(1:1,"*engineer*")>0
>
> else select row 5 and use
>
> =COUNTIF(5:5,"*engineer*")>0
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> "Gilles Desjardins" <[email protected]> wrote in message
> news:[email protected]...
> > Hi everyone, I've been struggling with this next request.
> >
> > Is it possible to color a row with a specific color based on finding a
> > specific word?
> > If in row 5 the word engineer shows up I would like to turn the whole
row
> > green. No matter where in row 5 the word shows up. I've tried
> > =IF(5:5="engineer",MOD(ROW(),2)=1,) and this works only for a word in
A5.
> > I can't seem to use A5:IV5
> > Any help would be appreciated. Gilles
> >
> >
>
>

[Gilles Desjardins]

To tell you the truth I don't know if that situation will arise. Your
solution is definitely more flexible.
Thanks again.

Gilles

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> So there is no chance that the word engineer will be part of a sentence,
> or
> have any other characters in the cell with it, it will ALWAYS be the ONLY
> entry in the cell yes?
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> "Gilles Desjardins" <[email protected]> wrote in message
> news:[email protected]...
>> Thank you all for your suggestions. Biff's suggestions is the one that
> hits
>> the nail on the head.
>>
>> Gilles
>> "Gilles Desjardins" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi everyone, I've been struggling with this next request.
>> >
>> > Is it possible to color a row with a specific color based on finding a
>> > specific word?
>> > If in row 5 the word engineer shows up I would like to turn the whole
> row
>> > green. No matter where in row 5 the word shows up. I've tried
>> > =IF(5:5="engineer",MOD(ROW(),2)=1,) and this works only for a word in
> A5.
>> > I can't seem to use A5:IV5
>> > Any help would be appreciated. Gilles
>> >
>>
>>
>
>

[Ken Wright]

No problem - just wanted you to be aware of the implications of one way over
another :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip>