Macro to change cell colour based on words
 

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?

Macro to change cell colour based on words
 

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

"tiger" wrote:

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?

Macro to change cell colour based on words
 

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.ColorInd ex = 3



JLGWhiz wrote:

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

"tiger" wrote:

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?


--

Dave Peterson

Macro to change cell colour based on words
 

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

--
Don Guillett Excel MVP
SalesAid Software

"tiger" wrote in message
...
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?