Video Discription |
Microsoft Excel Tutorial: How to Find and Replace the Color of a Word in Excel | MrExcel Podcast.
Welcome to another episode of the MrExcel podcast. In today's episode, we will be discussing how to find and replace the color of a certain word in Excel. This question came in from a listener who wanted to change the color of the word "fox" in a large number of cells to red. Sounds easy, right? Well, let's dive in and see how we can tackle this task.
At first, I thought this would be a simple task using the formula bar or the Find and Replace function (Ctrl+H). However, I quickly realized that this would not work as expected. The Find and Replace function was changing the entire cell, not just the word "fox". So, I decided to explore the Options and found that we can choose a specific format and change the color to red. But even this did not give the desired result. It was then that I thought of using Microsoft Word as a workaround.
Yes, you heard it right, Microsoft Word! We can simply copy the data from Excel and paste it into Word. Then, using the Find and Replace function (Ctrl+H) in Word, we can change the color of the word "fox" to red. The best part is that when we paste the data back into Excel, it gets pasted as a table, making it a seamless process. But if you want to take it a step further and go ultra geeky, then let's switch over to VBA.
In this video, I will show you a simple VBA macro that will help you change the color of a certain word in Excel. The macro will prompt you to enter the word you want to change and then loop through all the cells in the selection to find and replace the word with the desired color. However, there was an interesting issue that I encountered while writing this macro. If the word was not found, it would result in a runtime error. To prevent this, I had to come up with a solution using the On Error Resume Next function.
After a few attempts, I finally came up with a working macro that does the job perfectly. It even takes into account multiple occurrences of the word "fox" within a cell. So, if you want to give it a try, simply select the cells you want to operate on, run the macro, and enter the word you want to change. It's that simple! I hope this video helps you save time and effort when dealing with similar tasks in Excel. Thank you for watching and don't forget to subscribe to our channel for more Excel tips and tricks.
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
Table of Contents:
(00:00) Find and Replace Color of Certain Word
(00:23) Attempting to Use Find and Replace
(00:37) Using Microsoft Word as a Solution
(01:05) Going Ultra Geeky with VBA
(02:00) Dealing with Runtime Error 1004
(03:00) Final VBA Solution
(04:05) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Excel 2003
Excel 2007 and newer
Fourth and fifth arguments of OFFSET
IF function
Learn Excel
MrExcel podcast
OFFSET function
Second and third arguments of OFFSET
SUM function
Swiss Army knife
Variable length range
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. http://www.mrexcel.com/store/index.php?l=product_detail&p=257
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. http://www.mrexcel.com/store/index.php?l=product_detail&p=254
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs! |