Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Excel Macro to highlight words?


(!)

Tannerx007's Avatar
Tannerx007 Tannerx007 is offline
Member with 3 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Beginner
09-Jul-2012, 11:57 AM #1
Question Excel Macro to highlight words?
Hey guys and gals,

Here's what I'm trying to do: I have an Excel spread sheet with 3 columns and many rows depending on the data. I need to be able to search the data in column C for about 10 or so words and highlight them. The problem is, the words in column C have been exported by a program so each word ends with a comma then the next word begins with no space so it's difficult to search. Example, Text1,Text2,Text3 and so on. Is there a macro or software that I can use that will search all the rows for column C and highlight the group of words I choose? I'm using Excel 2007.

Thanks for your help!
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,298 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
09-Jul-2012, 06:20 PM #2
are you searching for text1 in the example or for all words text1 and text2 and text3

also what version of excel

you can do with a large nested if for 10 words - but not on 2003 version or below

problem is is looking for those characters so
if you had

doc,doctor
a search for doc - would also pick up doctor

ie Text10 would be found if you search for text1

in a conditional format in cell C4 I put this
Code:
=IF(OR(ISNUMBER(SEARCH("text1",C4)),ISNUMBER(SEARCH("text2",C4)),ISNUMBER(SEARCH("text3",C4)),ISNUMBER(SEARCH("text4",C4)),ISNUMBER(SEARCH("text5",C4)),ISNUMBER(SEARCH("text6",C4)),ISNUMBER(SEARCH("text7",C4)),ISNUMBER(SEARCH("text8",C4)),ISNUMBER(SEARCH("text9",C4)),ISNUMBER(SEARCH("text10",C4)),ISNUMBER(SEARCH("text11",C4))),TRUE,FALSE)
basically an IF statement to test for true or flase
a search statement to find the text
and isnumber to stop errors

as i say this will return a true for text19
as it can find the string text1
__________________
Wayne
Please let us know what the final solution was to any problem posted

Last edited by etaf; 09-Jul-2012 at 06:26 PM..
Tannerx007's Avatar
Tannerx007 Tannerx007 is offline
Member with 3 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Beginner
09-Jul-2012, 06:27 PM #3
Well the Text1,Text2,Text3 etc. are actually people's names. I have a group of about 6 or 7 people that I need to find throughout all these columns so the names that I need to find are always the same but the total amount of names in Column C can vary. So to answer your question, I will need to extract certain names. For example, I want to search all the rows in column C and have only say Text1 and Text3 highlighted so that it would look like this: Text1,Text2,Text3 (Where the underline would be the highlighted part.) Does that make sense? I'm using Excel 2007.

Last edited by Tannerx007; 09-Jul-2012 at 06:29 PM.. Reason: Forgot some info.
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,298 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
09-Jul-2012, 06:30 PM #4
sounds like amacro is needed
any reason you cant expand the text across columns so each cell has only one name

text to columns and use the , as the deliminator would then move all that information, so that one name would move into a cell of its own
then apply the conditional format
Tannerx007's Avatar
Tannerx007 Tannerx007 is offline
Member with 3 posts.
THREAD STARTER
 
Join Date: Jul 2012
Experience: Beginner
09-Jul-2012, 06:32 PM #5
Hmm, I wasn't aware of that function. I'll try it tomorrow and see how it goes. Thanks for your help!
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,298 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
09-Jul-2012, 06:38 PM #6
its should be under data > text to column

Select the range you have
Go to Data | Text To Columns.
Excel 2007 >Data tab >Text To Columns command in the Data Tools group.
click Delimited
Click Next
select the , check box and clear the Tab check box

http://techtips-rs.blogspot.co.uk/20...-2007-tip.html
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
2007, excel, highlight, macro, office

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑