Excel - Find & Replace Color of A Certain Word: Episode 1714

preview_player
Показать описание
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.

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.



"The Learn Excel from MrExcel Podcast Series"

Рекомендации по теме
Комментарии
Автор

I’ve been looking for this trick all day. I have a report I run daily and I want “just those characters RED” . In each line. Thai you for this!

stacyyoung
Автор

I don't know how i can say thanks to you 🥰🥰

zhiaromer
Автор

Sub ChangeColorOfWord()
MyWord = InputBox("Enter the pattern you want to highlight")
MyLen = Len(MyWord)

For Each cell In Selection
    For i = 1 To Len(cell.Value)
        On Error Resume Next
        WF = 0
        WF = Application.WorksheetFunction.Find(MyWord, cell.Value, i)
        On Error GoTo 0
        If WF > 0 Then
            cell.Characters(Start:=WF, Length:=MyLen).Font.Color = RGB(255, 0, 0)
            cell.Characters(Start:=WF, Length:=MyLen).Font.Bold = True
            i = WF + 1
        Else
            Exit For
        End If
    Next i
Next cell
End Sub

ManuelSoutoPico
Автор

Thank you! I been trying to do this for hours until I saw your video!

mzhang
Автор

Thank you so much! In my case, I was looking for a code in which specific words in the Sheet would be colored as soon as written, without using a InputBox. So I have changed the code a little. If someone is in the same situation, here is the code

Private Sub Target As Range)
Dim SrchRng1 As Range
Dim cell1 As Range
Set SrchRng1 = Range("E10:E10000")
Const MyWord1 As String = "apple" 'the word that would be colored
MyLen1 = Len(MyWord1)
For Each cell1 In SrchRng1
For i = 1 To Len(cell1.Value)
On Error Resume Next
WF1 = 0
WF1 = Application.WorksheetFunction.Find(MyWord1, cell1.Value, i)
On Error GoTo 0
If WF1 > 0 Then
cell1.Characters(WF1, MyLen1).Font.Color = -4165632 'the code color here. In this case is blue
i = WF1 + 1
Else
Exit For
End If
Next i
Next cell1
End Sub

bianca.sartori
Автор

Hey Bill! Great solution with VBA!
In my view, change line wf + i = 1 to i = wf, to catch a character if that were the case!
I am fascinated by your videos, keep it up!

JohnVergaraD
Автор

thank you this iwas the one i was looking for

venkatrayudu
Автор

hi there, where can i copy the macro text?

subhaniamsubhan
Автор

Hello, It is Great solution,
I need code that word/string color should change only when the full word matched.
Example: search word is 'fox'
1) Font color should change if any word 'fox' exact match in the excel range
2) Font color should not change if any word found as 'Foxy' or 'afox' in the excel range..

ganesanp
Автор

is there a way to get a "True" or "False" value by comparing the colour of 2 different cells? For Example, if Cell A1 and B1 are both filled in with Red then cell C1 should say "Match" or "True"?

EyeIn_The_Sky
Автор

If there are different amounts and if we want to highlight only amount instead of fox word, how to highlight?

ghappyalways
Автор

but what if I wanted to change the colors of the numbers depending on a negative or positive value???

duckpablo
Автор

Thank you so much for this. It saved me hours of time.
I modified the code to look for specific text and then run the macro. Fixes them all in a flash

Dim FindWord As String
Dim MyLen As Integer
Dim i, j As Integer
Dim myList(1 To 20) As Variant

Sub ChangeColour()
'
' Once a new function is created, and variables have been
' edited, change the variable colour to RED
'


For j = 1 To 20

FindWord = myList(j)
MyLen = Len(FindWord)

Range("A1:A3000").Select

For Each cell In Selection
For i = 1 To Len(cell.Value)
On Error Resume Next
WF = 0
WF = Application.WorksheetFunction.Find(FindWord, cell.Value, i)
On Error GoTo 0
If WF > 0 Then
cell.Characters(Start:=WF, Length:=MyLen).Font.Color = RGB(255, 0, 0)
i = WF + 1
Else
Exit For
End If
Next i
Next cell
Next j

[A1].Select

End Sub

jacquesvermaak
Автор

I'm working in word already, and this worked perfectly. Is there a way to change a number (always a random number) after the word fox? Example: fox 23, bear 1, fox 398, frog 12, fox 15. I want to change the number to the same color of the word fox.

LissaConley
Автор

hi, I have question, looking through below replys, there is similar one, Suppose we have lots of words (for example: one, four, ten, John, Vicky etc.) in red colour and we need to convert in single word like "ok" with black colour.. is it possible???, Let me ask you I want to know how it is possible to make each text has different color. For example, in sentence, certain word groups like numbers one, two, three in yellow color and wind, snow, rain in red color all of them of course previously given its value like number group, climate group. So if a word belongs in certain group which make it change according to its value.








답글

sdj
Автор

Suppose we have lots of words (for example: one, four, ten, John, Vicky etc.) in red colour and we need to convert in single word like "ok" with black colour.. is it possible???

themarathe
Автор

When I try this code the letter "i" which is in lowercase by your example, is automatically being changed to an uppercase, which is preventing the code to work. Anyone knows why or how to fix it please?

JaKoMaGrp