how can you do a conditional formatting on a cell that contains words?
ie. I want to highlighted all the cell which contains hotel , such as
happy hotel
holiday inn hotel
hotel hyatt
club hotel resort
(these are all highlighted)
can we do that?
how can you do a conditional formatting on a cell that contains words?
ie. I want to highlighted all the cell which contains hotel , such as
happy hotel
holiday inn hotel
hotel hyatt
club hotel resort
(these are all highlighted)
can we do that?
Hi
Highlight the column (say, A) and then try something like this:
=NOT(ISERROR(FIND("hotel",A1)))
Use the 'Formula is' option.
Andy.
<[email protected]> wrote in message
news:[email protected]...
> how can you do a conditional formatting on a cell that contains words?
>
> ie. I want to highlighted all the cell which contains hotel , such as
> happy hotel
> holiday inn hotel
> hotel hyatt
> club hotel resort
> (these are all highlighted)
>
> can we do that?
>
Use a formula of
=ISNUMBER(FIND("hotel"),A1)
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
<[email protected]> wrote in message
news:[email protected]...
> how can you do a conditional formatting on a cell that contains words?
>
> ie. I want to highlighted all the cell which contains hotel , such as
> happy hotel
> holiday inn hotel
> hotel hyatt
> club hotel resort
> (these are all highlighted)
>
> can we do that?
>
This should work. Select the first cell in your range (in my example A1). Go to Conditional Formatting. Change the CF option from Cell Value is to Formula is. Enter.
=SEARCH("hotel",A1,1)
Format>Patterns and pick the color you'd like. Click OK. Copy this format to the rest of your range.
HTH
Steve
wow
thats very helpul, thanks!!
Bob Phillips wrote:
> Use a formula of
>
> =ISNUMBER(FIND("hotel"),A1)
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > how can you do a conditional formatting on a cell that contains words?
> >
> > ie. I want to highlighted all the cell which contains hotel , such as
> > happy hotel
> > holiday inn hotel
> > hotel hyatt
> > club hotel resort
> > (these are all highlighted)
> >
> > can we do that?
> >
HI,
what if I want to highlight all the cell that contains hotel, villa,
resort and apartments?
how can i do that?
eg.
Apple hotel
banana hostel
caravan apartment
diana villa
how can i highlight apple hotel, caravan apartment and diana villa
using conditional formatting?
thanks.
SteveG wrote:
> This should work. Select the first cell in your range (in my example
> A1). Go to Conditional Formatting. Change the CF option from Cell
> Value is to Formula is. Enter.
>
> =SEARCH("hotel",A1,1)
>
> Format>Patterns and pick the color you'd like. Click OK. Copy this
> format to the rest of your range.
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=547883
haha i think i know how to do it
just by clicking 'ADD', adding another condition right?
[email protected] wrote:
> HI,
> what if I want to highlight all the cell that contains hotel, villa,
> resort and apartments?
> how can i do that?
>
> eg.
> Apple hotel
> banana hostel
> caravan apartment
> diana villa
>
> how can i highlight apple hotel, caravan apartment and diana villa
> using conditional formatting?
>
>
> thanks.
>
> SteveG wrote:
>
> > This should work. Select the first cell in your range (in my example
> > A1). Go to Conditional Formatting. Change the CF option from Cell
> > Value is to Formula is. Enter.
> >
> > =SEARCH("hotel",A1,1)
> >
> > Format>Patterns and pick the color you'd like. Click OK. Copy this
> > format to the rest of your range.
> >
> > HTH
> >
> > Steve
> >
> >
> > --
> > SteveG
> > ------------------------------------------------------------------------
> > SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> > View this thread: http://www.excelforum.com/showthread...hreadid=547883
hey what if I have more than 3 conidtions? as it only allows us to
add 3 coniditions..?
[email protected] wrote:
> haha i think i know how to do it
> just by clicking 'ADD', adding another condition right?
>
>
>
> [email protected] wrote:
>
> > HI,
> > what if I want to highlight all the cell that contains hotel, villa,
> > resort and apartments?
> > how can i do that?
> >
> > eg.
> > Apple hotel
> > banana hostel
> > caravan apartment
> > diana villa
> >
> > how can i highlight apple hotel, caravan apartment and diana villa
> > using conditional formatting?
> >
> >
> > thanks.
> >
> > SteveG wrote:
> >
> > > This should work. Select the first cell in your range (in my example
> > > A1). Go to Conditional Formatting. Change the CF option from Cell
> > > Value is to Formula is. Enter.
> > >
> > > =SEARCH("hotel",A1,1)
> > >
> > > Format>Patterns and pick the color you'd like. Click OK. Copy this
> > > format to the rest of your range.
> > >
> > > HTH
> > >
> > > Steve
> > >
> > >
> > > --
> > > SteveG
> > > ------------------------------------------------------------------------
> > > SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> > > View this thread: http://www.excelforum.com/showthread...hreadid=547883
That's it but you are limited to 3 CF's.
Regards,
Steve
That's it but you are limited to 3 CF's.
Regards,
Steve
thank you very much
SteveG wrote:
> That's it but you are limited to 3 CF's.
>
> Regards,
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=547883
you could use
=OR(ISNUMBER(SEARCH("hotel",A1)),ISNUMBER(SEARCH("villa",A1)),ISNUMBER(SEARC
H("resort",A1)),ISNUMBER(SEARCH("apartments",A1)))
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
<[email protected]> wrote in message
news:[email protected]...
> HI,
> what if I want to highlight all the cell that contains hotel, villa,
> resort and apartments?
> how can i do that?
>
> eg.
> Apple hotel
> banana hostel
> caravan apartment
> diana villa
>
> how can i highlight apple hotel, caravan apartment and diana villa
> using conditional formatting?
>
>
> thanks.
>
> SteveG wrote:
>
> > This should work. Select the first cell in your range (in my example
> > A1). Go to Conditional Formatting. Change the CF option from Cell
> > Value is to Formula is. Enter.
> >
> > =SEARCH("hotel",A1,1)
> >
> > Format>Patterns and pick the color you'd like. Click OK. Copy this
> > format to the rest of your range.
> >
> > HTH
> >
> > Steve
> >
> >
> > --
> > SteveG
> > ------------------------------------------------------------------------
> > SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
> > View this thread:
http://www.excelforum.com/showthread...hreadid=547883
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks