Hi,

I have been trying to get all the cells in a row to change from one
background colour to another dependent upon the the last cell in the row
containing the word "Yes".

So for example, all cells in all rows are green. But when the last cell in
say row 5 contains the word "Yes" all the cells in row 5 need to change to
yellow.

I have managed this on a row by row basis by selecting all cells in a row
and using conditional formatting with formula =$I$1="Yes" on row 1,
=$I$2="Yes" on row 2 etc etc.

I have 1000 rows and I don't want to have to enter this on a row by row
basis so unless someone knows how to get conditional formatting to increment
the "$I$1" to "$I$2" etc I need another fix...

Thanks

select all your cells (multiple rows and columns) and format according to
the activecell (assume A1 is the active cell)

the formula would be

=$I1="Yes"

--
Regards,
Tom Ogilvy


"MAS" wrote in message
...
Hi,

I have been trying to get all the cells in a row to change from one
background colour to another dependent upon the the last cell in the row
containing the word "Yes".

So for example, all cells in all rows are green. But when the last cell in
say row 5 contains the word "Yes" all the cells in row 5 need to change to
yellow.

I have managed this on a row by row basis by selecting all cells in a row
and using conditional formatting with formula =$I$1="Yes" on row 1,
=$I$2="Yes" on row 2 etc etc.

I have 1000 rows and I don't want to have to enter this on a row by row
basis so unless someone knows how to get conditional formatting to
increment
the "$I$1" to "$I$2" etc I need another fix...

Thanks

Remove the $ sign before the number to change the reference of the row from
absolute to relative. Then when you copy the format to the other rows the
number will increment (but the colum will remain absolute).

=$I1="Yes"
--
HTH...

Jim Thomlinson


"MAS" wrote:

Hi,

I have been trying to get all the cells in a row to change from one
background colour to another dependent upon the the last cell in the row
containing the word "Yes".

So for example, all cells in all rows are green. But when the last cell in
say row 5 contains the word "Yes" all the cells in row 5 need to change to
yellow.

I have managed this on a row by row basis by selecting all cells in a row
and using conditional formatting with formula =$I$1="Yes" on row 1,
=$I$2="Yes" on row 2 etc etc.

I have 1000 rows and I don't want to have to enter this on a row by row
basis so unless someone knows how to get conditional formatting to increment
the "$I$1" to "$I$2" etc I need another fix...

Thanks

Hi MAS

try this
Sub ColNr()

Dim i As Long
Dim kolA As Double


kolA = Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To kolA
If Range("I" & i) = "Yes" Then
Range("A" & i & ":I" & i).Interior.ColorIndex = 45
End If
Next i

End Sub

Regards Yngve