Bill received a question this morning via the Learn Excel from MrExcel YouTube Account: "I want to change the Color of the word 'Fox' in multiple cells; how can this be done quickly?" Moving right into action, MrExcel begins by showing the straight forward, manual approach using the Formula Bar, then moves on to other means to accomplish the task. Follow along with Episode #1714 as Bill delves into solutions using Find and Replace, Microsoft Word, VBA and more.
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2013, check out... "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Excel 2013 In Depth
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2013, check out... "Microsoft 2013 InDepth" -- by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013. Excel 2013 In Depth
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL".
Learn Excel from MrExcel podcast, episode 1714 - Find and Replace Color of Certain Word.
I have to tell you the way this question came in.
This morning I said "Oh well, this is going to be really, really easy". That person said "the quick brown fox jumps over the lazy dog" in a whole bunch of cells, I want to change the color of the word "fox" in all of those cells to red.
Huh! OK well, you know, we can do this up at the formula bar, change the color for a certain word.
But if we're going to try and use Find and Replace, Ctrl+H, replace a recurrence of fox with fox(red), I thought well, maybe he hasn't seen the Options.
And I'm here in the Options, we can choose a specific Format, and choose the color red.
So hey, we're going to change their recurrence of fox in any format to fox in red, I do replace all.
Oh.
Now it's changing the whole cell, it's not just changing that word like I want it to do, or like we're trying to do, so Ctrl+Z to undo that boy.
I hate to do this, but one solution to this is to copy the data from Excel, and open this other tiny little program that you might have on your computer.
You probably have never opened it, it's called Microsoft Word! We'll paste into Word, and then Ctrl+H in Word, we're going to change fox to fox! Ah, but this 2nd fox, we're going to go down here, Format, Font, and change the color to red.
So now we're changing any occurrence of Fox to Fox in red, Replace All, and Word does what Excel can't do.
Oh, I hate to say that.
The beautiful thing about Excel data being pasted to Word, it gets pasted as a table.
So Ctrl+C. it will go back and round-trip perfectly to Excel, Ctrl+V, problem solved.
But what if you want to go ultra geeky?
Then let's switch over to VBA, and this little ColorChange macro here.
So my idea was, we'd would pop up an InputBox, say "Hey, what word you want to change?" And then figure out how long that is.
Why do we have to figure out how long it is?
I use the macro recorder to see that we want to do cell.Characters, start as a certain position, length of a certain position .Font.Color .
Equals this case, RGB, 255 is the red that I use.
You can put in a combination of red, green, and blue in there.
Alright and, so that was simple enough, loop through all the cells in selection, for each cell in selection, you know, do the find, and so on.
However, there is an interesting, interesting situation here, if you try and do a Find and the word is found, it works great, but if you try and do a Find and the word is not found, then you get this runtime error 1004.
And so I had to try and find a way to prevent that.
So of course, On Error Resume Next, alright?
But here's what I found, WF starts out empty, we run the first find, and it says that it's found at 14.
We run the second find, and On Error Resume Next prevents the error, that's great, but WF never gets reset, it's left at 14.
And so, in my first few steps of this macro, I changed the word fox there to red, and then when something wasn't found, I was changing those same three characters to red over and over and over again.
So here's the final macro I came up with, we do have the On Error Resume Next, I reset the WF, where found variable to zero, and then do the Find and then check to see if it's >0, do the replace.
And then oh, by the way, I'm looping from 1 to the length of the cell looking for multiple occurrences of fox, go ahead and increment that loop variable up to where found +1.
I guess I could really do where found plus the length, but I tried to think of some word that it might occur.
I don't know, this is the way I did it, there's probably better ways.
In fact I wouldn't be surprised if there's just a completely massively better way to do the whole thing that I'm missing.
Like I said "This one will take two minutes to solve".
And here I am three hours later, finally recording the podcast.
Ugh! So, choice #1: go to Word to the Find and Replace in Word, boy, that seems horrible.
Choice #2: this quick little macro here, let's give it a try.
Alright, so to try the macro, select all the cells that you want to operate on, Alt+F8 to show the macro dialogue box, ColorChange, run, Word to change: fox, click OK.
And it goes through, finds fox everywhere, even when there's two, when it finds the word fox inside another word, it changes just those letters, and seems to be good to go.
Alright, so Word or VBA.
Want to thank you for stopping by today.
Boy, what a long, arduous podcast for what sounded like a really easy question, at least it doesn't seem to be easy in Excel.
See you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1714 - Find and Replace Color of Certain Word.
I have to tell you the way this question came in.
This morning I said "Oh well, this is going to be really, really easy". That person said "the quick brown fox jumps over the lazy dog" in a whole bunch of cells, I want to change the color of the word "fox" in all of those cells to red.
Huh! OK well, you know, we can do this up at the formula bar, change the color for a certain word.
But if we're going to try and use Find and Replace, Ctrl+H, replace a recurrence of fox with fox(red), I thought well, maybe he hasn't seen the Options.
And I'm here in the Options, we can choose a specific Format, and choose the color red.
So hey, we're going to change their recurrence of fox in any format to fox in red, I do replace all.
Oh.
Now it's changing the whole cell, it's not just changing that word like I want it to do, or like we're trying to do, so Ctrl+Z to undo that boy.
I hate to do this, but one solution to this is to copy the data from Excel, and open this other tiny little program that you might have on your computer.
You probably have never opened it, it's called Microsoft Word! We'll paste into Word, and then Ctrl+H in Word, we're going to change fox to fox! Ah, but this 2nd fox, we're going to go down here, Format, Font, and change the color to red.
So now we're changing any occurrence of Fox to Fox in red, Replace All, and Word does what Excel can't do.
Oh, I hate to say that.
The beautiful thing about Excel data being pasted to Word, it gets pasted as a table.
So Ctrl+C. it will go back and round-trip perfectly to Excel, Ctrl+V, problem solved.
But what if you want to go ultra geeky?
Then let's switch over to VBA, and this little ColorChange macro here.
So my idea was, we'd would pop up an InputBox, say "Hey, what word you want to change?" And then figure out how long that is.
Why do we have to figure out how long it is?
I use the macro recorder to see that we want to do cell.Characters, start as a certain position, length of a certain position .Font.Color .
Equals this case, RGB, 255 is the red that I use.
You can put in a combination of red, green, and blue in there.
Alright and, so that was simple enough, loop through all the cells in selection, for each cell in selection, you know, do the find, and so on.
However, there is an interesting, interesting situation here, if you try and do a Find and the word is found, it works great, but if you try and do a Find and the word is not found, then you get this runtime error 1004.
And so I had to try and find a way to prevent that.
So of course, On Error Resume Next, alright?
But here's what I found, WF starts out empty, we run the first find, and it says that it's found at 14.
We run the second find, and On Error Resume Next prevents the error, that's great, but WF never gets reset, it's left at 14.
And so, in my first few steps of this macro, I changed the word fox there to red, and then when something wasn't found, I was changing those same three characters to red over and over and over again.
So here's the final macro I came up with, we do have the On Error Resume Next, I reset the WF, where found variable to zero, and then do the Find and then check to see if it's >0, do the replace.
And then oh, by the way, I'm looping from 1 to the length of the cell looking for multiple occurrences of fox, go ahead and increment that loop variable up to where found +1.
I guess I could really do where found plus the length, but I tried to think of some word that it might occur.
I don't know, this is the way I did it, there's probably better ways.
In fact I wouldn't be surprised if there's just a completely massively better way to do the whole thing that I'm missing.
Like I said "This one will take two minutes to solve".
And here I am three hours later, finally recording the podcast.
Ugh! So, choice #1: go to Word to the Find and Replace in Word, boy, that seems horrible.
Choice #2: this quick little macro here, let's give it a try.
Alright, so to try the macro, select all the cells that you want to operate on, Alt+F8 to show the macro dialogue box, ColorChange, run, Word to change: fox, click OK.
And it goes through, finds fox everywhere, even when there's two, when it finds the word fox inside another word, it changes just those letters, and seems to be good to go.
Alright, so Word or VBA.
Want to thank you for stopping by today.
Boy, what a long, arduous podcast for what sounded like a really easy question, at least it doesn't seem to be easy in Excel.
See you next time for another netcast from MrExcel!
