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:

Please Login or Register  to view this content.

**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.) :

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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