Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
select a column, then add rows of each column | Excel Discussion (Misc queries) | |||
how to select a single column when this column cross a merged cell | Excel Programming | |||
Shortcut to select column with data in previous column | Excel Discussion (Misc queries) | |||
select detail in column B if column A = date | Excel Worksheet Functions |