Solved

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

Posted on 2013-02-04
9
451 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
[X]
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
9 Comments
 
LVL 48

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
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
0
 

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

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