How to find a string and change it's color?

**jp001** · 05-12-2009, 07:08 AM

Can anyone tell me which command would find a certain word in a range, then change it's color please?

**sweep** · 05-12-2009, 07:29 AM

Hi,

Try this:

Sub a()
Dim rCell As Range
Dim finder As Variant
finder = InputBox("Text to find")
For Each rCell In Selection
If InStr(rCell.Value, finder) > 0 Then rCell.Characters(InStr(rCell.Value, finder), Len(finder)).Font.ColorIndex = 4
Next rCell
End Sub

**jp001** · 05-12-2009, 08:08 AM

Thanks sweep! That looks like it should work, but I'm getting an error
when I try it (with some adjustments to fit the code I'm trying out here.) :


Sub a()
Dim rCell As Range
Dim lRow As Long
Dim finder As Variant
finder = "End"

For lRow = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
rCell = Cells(lRow, 1).Value

If InStr(rCell.Value, finder) > 0 Then
    rCell.Characters(InStr(rCell.Value, finder), Len(finder)).Font.ColorIndex = 4
End If

Next lRow

End Sub

I'm getting the error: "Object variable or With block variable not set" on the line: rCell = Cells(lRow, 1).Value.

If I Dim rCell as a variant, I get the error "Object required" on the line:
"If InStr(rCell.Value, finder) > 0 Then"

Am I making some glaring mistake somewhere here please?

**Andy Pope** · 05-12-2009, 08:19 AM

rCell is a range object and requires the Set command to make the assignment

set rCell = Cells(lRow, 1)

**c.vaibhav** · 05-12-2009, 08:22 AM

Hi,

The mistake that you are making is that you are declaring rCell as range and then you are using it as a string..

Try this code instead

Sub a()
Dim rCell As Range
Dim lRow As Long
Dim finder As Variant
finder = "End"

For lRow = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    Set rCell = Range(Cells(lRow, 1), Cells(lRow, 1))
    
    If InStr(rCell.Value, finder) > 0 Then
        rCell.Characters(InStr(rCell.Value, finder), Len(finder)).Font.ColorIndex = 4
    End If

Next lRow

End Sub

HTH,
Vaibhav

**jp001** · 05-12-2009, 08:36 AM

Thanks for that guys! Works fine now

I assume btw that it will work similarly if I make 'finder' an
array of values, so I can change the color of multiple words using
this code? (I'm doing this as an exercise to color my code before
printing it out.) I'll test that out later anyway as it's nearly
midnight here now