Macro to change cell colour based on words

[tiger]

I have a cell identified by either the word "Unbene" or the interior cell
colour.

I want to write a macro that will identify that cell and then colour 9 cells
in the same row.

The word is in cell C and I want cells A-I change to a colour I designate.

Is this clearer?

 

[JLGWhiz]

This is kind of bulky, but it works.

Sub findWord()
lastRow = Cells(Rows.Count, 3).End(xlUp).Row
For Each c In Range("C2:C" & lastRow)
If c = "Unbene" Then
x = c.Address
Range("A" & Range(x).Row & ":I" & Range(x).Row).Interior.ColorIndex = 3
End If
Next
End Sub

 

[Dave Peterson]

Maybe less bulky:

If c = "Unbene" Then
c.offset(0,-2).resize(1,9).Interior.ColorIndex = 3
End If

or even

c.entirerow.cells(1).resize(1,9).Interior.ColorIndex = 3

 

[Don Guillett]

Another
If only ONE, then find might be best. If not, use the loop provided or
FINDNEXT.

myrow=columns("c").find("Unbene").row
range(cells(myrow,"a"),cells(myrow,"i")).interior.colorindex=6