Solved Find & change a word's font color in Excel 2016

Microsoft Excel 2016 suites
September 9, 2016 at 09:24:23
Specs: Windows 10
Can Excel 2016 find and replace a word's (character string) font color?

I need the capability to search a spreadsheet for a word and change that word's font color. For example, find all instances of the word "ANALYZE" and change the font color to BLUE (0000FF). Similarly, find all instances of the word "DESIGN" and change its font color to GREEN (009900), and so on.

I'm doing it manually now. Would like to run a macro (?) that does it. It would save me 15 minutes of keyboarding time, 10 times a week.

Thanks, Rosey

message edited by RoseyProcessTraige


See More: Find & change a words font color in Excel 2016

Reply ↓  Report •


✔ Best Answer
September 13, 2016 at 06:44:24
I can not access google drive files at my current location. I will try to take a look this evening, EST.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 9, 2016 at 09:53:58
Use Conditional Formatting.

Select the range that you are "searching" and use the following steps. These are for Excel 2010 but should be similar for 2016.

Conditional Formatting
New Rule
Select a Rule Type: Format only cells that contain
Cell Value...equal to...ANALYZE
Click the Format button
Font Tab
Color drop down
Pick a Color
OK...OK

Repeat for DESIGN

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#2
September 9, 2016 at 10:00:37
Tried it. It set the color of the entire cell, not exclusively the word I searched on.

Reply ↓  Report •

#3
September 9, 2016 at 10:07:04
Did you choose the Fill tab or the Font tab?

On my 2010 system, I'm looking at cells where only the font color of the contents
are formatted because I chose the Font tab and then chose a color.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
September 9, 2016 at 10:07:56
Works for me.

Make sure you select the FONT tab in the Format Cells popup window.

Then use the drop down box, below the word "Color", in the middle, right side of the popup.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#5
September 9, 2016 at 10:12:39
Mike: Are you running 2016?

I have 2010 and 2013, only tried this on 2010.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
September 9, 2016 at 10:52:51
I'm still on 2007.
Have no urgent need to upgrade

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#7
September 9, 2016 at 11:39:01
Same result; all characters in the cell change to the selected color (same as your result). It's a start.

Clarification: I only want the characters in the desired (searched) word to change color, leaving the remaining words (characters) unchanged.

Thanks for your time.

Rosey


Reply ↓  Report •

#8
September 9, 2016 at 12:15:22
I see. Your previous post was not clear. When we heard "It set the color of the entire cell, not exclusively the word I searched on" we assumed you meant the fill color of the cell itself, not the contents of the cell.

There's a big difference between a cell and the contents of a cell.

In that case, you will indeed need a macro. That will take a little time, but I'll work on something over the weekend as time allows.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#9
September 9, 2016 at 12:20:35
@DerbyDad03,

If it's a material amount of time, contact me at xxx.xxxx.com. May have budget to assist.

edited by moderator: Removed email address


Reply ↓  Report •

#10
September 9, 2016 at 12:35:25
Thank you for the offer, but payment will not be necessary.

I have edited your post to remove the email address. We do not like to see clear text email addresses as we do not want this forum to become known as a place where email addresses can be harvested. Once the bad guys start hanging around, it's really hard to get rid of them.

In the future, if you want to send someone any "personal" information, please do it via Private Message.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#11
September 9, 2016 at 18:24:14
understood. thanks, great site

Reply ↓  Report •

#12
September 12, 2016 at 07:21:04
Just an update...

I worked on this over the weekend and got close. I have the code for changing the font color of individual characters within a string, but I still need to work on an efficient method for the user to enter the search string and choose the desire font color.

If hard-coding specific words and specific colors is acceptable, then that's basically done. However, since you listed 2 "For example" words and ended with "and so on", I assume that there are more than 2 words and that they are not always the same.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#13
September 12, 2016 at 08:24:03
Funny you should ask. Actually, there are five and only five words, always in ALL CAPS:

ANALYZE (change color to 0000FF -- Blue)
DESIGN to 009900 -- Green
TRAIN to 990099 -- Purple
ENFORCE to FF0000 - Red
IMPLEMENT to CC0099 -- Magentaish?


Reply ↓  Report •

#14
September 12, 2016 at 09:03:23
Do you want to highlight every occurrence of every word every time or do you want multiple macros, one for each word?

How strict are you concerning the exact color values you used? I am not familiar with the values you used (VBA does not recognize them as either HEX or Octal values), but I can certainly work with Blue, Green, Red, etc.

Are there combinations of those words in any given cell, e.g.

I want to ANALYZE the DESIGN as a means to TRAIN employees so that they 
can ENFORCE the rules that state that users must IMPLEMENT the proper 
process.

Are there ever more than one occurrence of a word in a given cell:

In order to understand what is happening, you need to ANALYZE, ANALYZE 
and then ANALYZE again!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#15
September 13, 2016 at 04:21:25
A cell may have any of these five words.

See Column F in the following example: https://drive.google.com/file/d/0B3...

The HEX colors are at http://html-color-codes.com/


Reply ↓  Report •

#16
September 13, 2016 at 06:44:24
✔ Best Answer
I can not access google drive files at my current location. I will try to take a look this evening, EST.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#17
September 14, 2016 at 19:35:17
Try the following code and let me know what you think.

A few points to mention:

1 - I could not get your hex color codes to work. VBA deals with hex codes differently than HTML. I did some web searching and it looks like some serious conversions have to be done in order to use your hex codes in VBA, so I used to equivalent RGB code found on the website that you provided.

That said, the color of the word "Train" in D37 (and other places) does not match the color of "Train" in F5:F31 after the macro is run. According to the website you provided, the RGB equivalent of 990099 is 153,0,153, but that does not result in the same color "Train" as you have outside F5:F31. I'll leave it to you to find an RGB code that you like.

2 - I see that the word IMPLEMENT is spelt wrong in a number of cells within F5:F31. (IMPLMENT). The code will not find those words so the color will not be changed. There may be other misspellings, I didn't check.

3 - The first thing the code does is reset all the font colors in F5:F31 to Automatic (Black). I did this mainly for testing purposes so I could see the effect that the code had on the range. If you do not want this to happen, then delete theses 2 lines:

'Reset all colors in F5:F31
    myRange.Font.ColorIndex = xlAutomatic

Let me know what you think.

Option Explicit
Sub ColorMyWord()

Dim startChar As Integer, _
    lenColor As Integer, _
    nxtWord As Integer
Dim w As Range, _
    myRange As Range
Dim dRed As Integer, _
    dBlue As Integer, _
    dGreen As Integer
Dim firstAddress As String, _
    srchWord As String

Set myRange = Sheets("SME Team SMalls & Bigs").Range("F5:F31")

'Reset all colors in F5:F31
    myRange.Font.ColorIndex = xlAutomatic

'Loop through 6 Cases, setting search word and RGB color codes
    For nxtWord = 1 To 6
      Select Case nxtWord
        Case 1
          srchWord = "ANALYZE"
           dRed = 0
           dBlue = 0
           dGreen = 255 '0000FF Blue
        Case 2
          srchWord = "DESIGN"
           dRed = 0
           dBlue = 153
           dGreen = 0 '009900 Green
        Case 4
          srchWord = "TRAIN"
           dRed = 153
           dBlue = 0
           dGreen = 153 '990099 Purple
        Case 5
          srchWord = "ENFORCE"
           dRed = 255
           dBlue = 0
           dGreen = 0 'FF0000 Red
        Case 6
          srchWord = "IMPLEMENT"
           dRed = 204
           dBlue = 0
           dGreen = 153 'CC0099 Magentaish?
      End Select

'Find search words and set font color
      With myRange
        Set w = .Find(srchWord, lookat:=xlPart, MatchCase:=True)
         If Not w Is Nothing Then
           firstAddress = w.Address
          Do
           startChar = InStr(1, w, srchWord)
              lenColor = Len(srchWord)
              w.Characters(Start:=startChar, Length:=lenColor).Font.Color = _
                     RGB(dRed, dBlue, dGreen)
              Set w = .FindNext(w)
          Loop While Not w Is Nothing And w.Address <> firstAddress
         End If
      End With
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#18
September 15, 2016 at 16:04:05
(Swamped with client work...) @ DerbyDad03...

I'll be able to spend uninterrupted time on this, this weekend. Send me a private message and I'll compensate you (assuming it works).

Great forum.


Reply ↓  Report •


Ask Question