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

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

On Wed, 4 Aug 2010 17:33:23 +0800, "ela" wrote:

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

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?

On Thu, 5 Aug 2010 08:35:24 +0800, "ela" wrote:

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.