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(promp
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
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(promp
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?????
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?????
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Never mind, I found a color palette online that showed a bunch of different colors.
ASKER
Sorry, I didn't see your post right above mine. That's the same exact site I found. :)
ASKER
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?
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?
Have you tried my solution which only looks the cells where data exists.
Kris
Kris
ASKER
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!!
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
to
Set Rng = Sheets("Name of sheet").Cells