Select last column
 

Hi,

I have some code that works perfectly. It checks the value of each row and
colours the entire row with the appropraite colour.

For lRow = 2 To 21
If Cells(lRow, 3).Value = "Yes" Then
Rows(lRow).EntireRow.Interior.ColorIndex = 50

Next lRow


What i need is for it so only colour the row until the last column. Further
to this not all rows will have data in all fields, I think i know how to
select the last column with data in it but I need t select the last column
with data in it from any row.

I hope that makes sense.

Any help is greatly appreciated.

Martin

Next lRow

Select last column
 

For lRow = 2 To 21
If Cells(lRow, 3).Value = "Yes" Then
Range(Cells(lRow, 3), Cells(lRow, Columns.Count).End(xlToLeft)).Interior.ColorIndex = 50
end if
Next lRow

HTH,
Bernie
MS Excel MVP


"Martin" wrote in message
...
Hi,

I have some code that works perfectly. It checks the value of each row and
colours the entire row with the appropraite colour.

For lRow = 2 To 21
If Cells(lRow, 3).Value = "Yes" Then
Rows(lRow).EntireRow.Interior.ColorIndex = 50

Next lRow


What i need is for it so only colour the row until the last column. Further
to this not all rows will have data in all fields, I think i know how to
select the last column with data in it but I need t select the last column
with data in it from any row.

I hope that makes sense.

Any help is greatly appreciated.

Martin

Next lRow

Select last column
 

Hi Martin,

It was completly clear to me.

Sub ColorRowOnYes()
Dim lRow As Long
Dim lCol As Long

For lRow = 2 To 21
If Cells(lRow, 3).Value = "Yes" Then
If IsEmpty(Cells(lRow, 256)) Then
lCol = Cells(lRow, 256).End(xlToLeft).Column
Else
lCol = 256
End If
Range(Cells(lRow, 1), Cells(lRow, lCol)).Interior.ColorIndex = 50
End If
Next
End Sub

HTH,

Wouter

Select last column
 

Thank you both.

For anyone else reading, Bernies code fills the one cell, RadarEye files all
cells in that row.

Martin

"RadarEye" wrote:

Hi Martin,

It was completly clear to me.

Sub ColorRowOnYes()
Dim lRow As Long
Dim lCol As Long

For lRow = 2 To 21
If Cells(lRow, 3).Value = "Yes" Then
If IsEmpty(Cells(lRow, 256)) Then
lCol = Cells(lRow, 256).End(xlToLeft).Column
Else
lCol = 256
End If
Range(Cells(lRow, 1), Cells(lRow, lCol)).Interior.ColorIndex = 50
End If
Next
End Sub

HTH,

Wouter