Solved

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

Posted on 2013-02-04
9
445 Views
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)
        Loop
Next cl
End Sub
0
Comment
Question by:esu4236
  • 5
  • 2
  • 2
9 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38852760
Change

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

to

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

Accepted Solution

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

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

Open in new window


Kris
0
 

Author Comment

by:esu4236
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?????
0
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 150 total points
ID: 38855343
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

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

Author Comment

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

Author Comment

by:esu4236
ID: 38855406
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
 
LVL 18

Expert Comment

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

Kris
0
 

Author Closing Comment

by:esu4236
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!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now