To color string of variable size

E

ela

The following codes color a character in green. Is there any way to color a
string of variable size, e.g. "abc", "ha, ha, I got you" etc?

Sub GreenLetter()
Dim s As String * 1
Dim c As Range
Dim i As Long

s = InputBox("Which letter to greenden?")

If s Like "[!A-Za-z]" Then
MsgBox ("Must specify a LETTER")
Exit Sub
End If

For Each c In Selection
With c
If .HasFormula Then .Value = .Text
For i = 1 To Len(.Text)
If LCase(Mid(.Text, i, 1)) = LCase(s) Then
.Characters(i, 1).Font.Color = RGB(30, 255, 15)
'Selection.Interior.ColorIndex = [A1].Interior.ColorIndex
End If
Next i
End With
Next c
End Sub
 
M

Master Blaster

This should work:

Sub GreenLetter()
Dim s As String
Dim c As Range
Dim i As Long


s = InputBox("Which letter to greenden?")
LL = Len(s)

If s Like "[!A-Za-z]" Then
MsgBox ("Must specify a LETTER")
Exit Sub
End If


For Each c In Selection
With c
If .HasFormula Then .Value = .Text
For i = 1 To Len(.Text)
If LCase(Mid(.Text, i, LL)) = LCase(s) Then
.Characters(i, LL).Font.Color = RGB(30, 255, 15)
Selection.Interior.ColorIndex =
[A1].Interior.ColorIndex
End If
Next i
End With
Next c
End Sub
 
R

Ron Rosenfeld

The following codes color a character in green. Is there any way to color a
string of variable size, e.g. "abc", "ha, ha, I got you" etc?

Sub GreenLetter()
Dim s As String * 1
Dim c As Range
Dim i As Long

s = InputBox("Which letter to greenden?")

If s Like "[!A-Za-z]" Then
MsgBox ("Must specify a LETTER")
Exit Sub
End If

For Each c In Selection
With c
If .HasFormula Then .Value = .Text
For i = 1 To Len(.Text)
If LCase(Mid(.Text, i, 1)) = LCase(s) Then
.Characters(i, 1).Font.Color = RGB(30, 255, 15)
'Selection.Interior.ColorIndex = [A1].Interior.ColorIndex
End If
Next i
End With
Next c
End Sub

Modifying your code a bit:

=====================
Option Explicit
Sub GreenPhrase()
Dim s As String
Dim c As Range
Dim i As Long, j As Long

s = InputBox("Which phrase to green?")
j = Len(s)

For Each c In Selection
With c
i = InStr(1, .Text, s)
.Font.ColorIndex = xlAutomatic
If i > 0 Then
.Characters(i, j).Font.Color = RGB(30, 255, 15)
'Selection.Interior.ColorIndex =
[A1].Interior.ColorIndex
End If
End With

Next c
End Sub
+++++++++++++++++++++++++++++
 
E

ela

Modifying your code a bit:
=====================
Option Explicit
Sub GreenPhrase()
Dim s As String
Dim c As Range
Dim i As Long, j As Long

s = InputBox("Which phrase to green?")
j = Len(s)

For Each c In Selection
With c
i = InStr(1, .Text, s)
.Font.ColorIndex = xlAutomatic
If i > 0 Then
.Characters(i, j).Font.Color = RGB(30, 255, 15)
'Selection.Interior.ColorIndex =
[A1].Interior.ColorIndex
End If
End With

Next c
End Sub
+++++++++++++++++++++++++++++

Is this modification making the code running faster?
 
R

Ron Rosenfeld

Modifying your code a bit:

=====================
Option Explicit
Sub GreenPhrase()
Dim s As String
Dim c As Range
Dim i As Long, j As Long

s = InputBox("Which phrase to green?")
j = Len(s)

For Each c In Selection
With c
i = InStr(1, .Text, s)
.Font.ColorIndex = xlAutomatic
If i > 0 Then
.Characters(i, j).Font.Color = RGB(30, 255, 15)
'Selection.Interior.ColorIndex =
[A1].Interior.ColorIndex
End If
End With

Next c
End Sub
+++++++++++++++++++++++++++++

Is this modification making the code running faster?

You will have to tell me when you run it against your data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top