Excel 2010 - Change one word in a cell to a different color

Posted on 2013-02-04
Last Modified: 2013-02-05
I found some vb coding that will allow me to change one word within a cell to the color red, which is what I want it to do; however, the coding will only let me change one column at a time.  How can I do the entire worksheet?  I tried changing the Range "A" to "A:Z" or beyond, but that didn't work.  I'm not a programmer, so need help.

Sub rita()
Dim Rng As Range, cl As Range, Red As Integer
Dim oStrg As String
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
On Error Resume Next
 oStrg = Application.InputBox(prompt:="Please Enter Word ", Title:="Find Word", Type:=2)
    If oStrg = "" Then Exit Sub
For Each cl In Rng
     Red = InStr(1, cl, oStrg, vbTextCompare)
        Do Until Red = 0
             With cl.Characters(Red, Len(oStrg))
             .Font.ColorIndex = 3
             .Font.Bold = True
             End With
             Red = InStr(Red + 1, cl, oStrg, vbTextCompare)
Next cl
End Sub
Question by:esu4236
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
LVL 48

Expert Comment

by:Martin Liss
ID: 38852760

Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))


Set Rng = Sheets("Name of sheet").Cells
LVL 18

Accepted Solution

krishnakrkc earned 350 total points
ID: 38853730
May be..

Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 3)

Open in new window


Author Comment

ID: 38855276
Okay, I'll try that - one more question.  How do I find out what number to use for each font color in this line:  .Font.ColorIndex = 3

I'm assuming 3 is for Red.  But what if I want to use Blue, Green, Orange, Purple as well?  Where do I find the numbers for those colors?????
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

LVL 48

Assisted Solution

by:Martin Liss
Martin Liss earned 150 total points
ID: 38855343

Author Comment

ID: 38855353
Never mind, I found a color palette online that showed a bunch of different colors.

Author Comment

ID: 38855356
Sorry, I didn't see your post right above mine.  That's the same exact site I found.  :)

Author Comment

ID: 38855406
Okay, I tried replacing the line

Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Set Rng = Sheets("Name of sheet").Cells, and putting in "Sheet1" for the worksheet name.  

It did change all incidences of my one word to red; however, then it proceeded to lock up and give me a Not Responding.  I tried it two times with the same results.  Any ideas why?
LVL 18

Expert Comment

ID: 38855918
Have you tried my solution which only looks the cells where data exists.


Author Closing Comment

ID: 38856137
Thanks to both of you for helping me out with this.  I greatly appreciate it.  I wanted to give some points to both of you since you both responded and helped me through this process.  I tried to be fair and hope that's okay.  THANKS AGAIN!!!!  Have a great day!!

Featured Post

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question