• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

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

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)
        Loop
Next cl
End Sub
0
esu4236
Asked:
esu4236
  • 5
  • 2
  • 2
2 Solutions
 
Martin LissOlder than dirtCommented:
Change

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

to

Set Rng = Sheets("Name of sheet").Cells
0
 
krishnakrkcCommented:
May be..

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

Open in new window


Kris
0
 
esu4236Author Commented:
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?????
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Martin LissOlder than dirtCommented:
0
 
esu4236Author Commented:
Never mind, I found a color palette online that showed a bunch of different colors.
0
 
esu4236Author Commented:
Sorry, I didn't see your post right above mine.  That's the same exact site I found.  :)
0
 
esu4236Author Commented:
Okay, I tried replacing the line

Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
with
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?
0
 
krishnakrkcCommented:
Have you tried my solution which only looks the cells where data exists.

Kris
0
 
esu4236Author Commented:
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!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now