Return the row number of a cell if its interior color = 50
 

I have a worksheet that I need to delete rows. The first row is always 10,
but the second row can vary past 10. I was wondering if there is quick
function or cleaner way of writing code to find the last row in Col.B that
contains an interior colorindex = 50? This is what I have.

Sub DeleteRows()

' find green line at end of quote sheet
lngLastRow = 10
Do Until Cells(lngLastRow, 2).Interior.ColorIndex = 50
lngLastRow = lngLastRow + 1
Loop

' delete rows (items) from quote sheet
Rows("10:" & lngLastRow).Delete Shift:=xlUp

End Sub
--
Cheers,
Ryan

Return the row number of a cell if its interior color = 50
 

The loop is in principle good.

From Excel 2002 onwards (if I'm not mistaken) you can search on formatting
of a cell with the usual Ctrl-F (you can program that as well in VBA).

Another option is, if the color is set with some logic behind it. Then that
logic could perhaps be used in an autofilter or a regular Find in VBA.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"RyanH" wrote:

I have a worksheet that I need to delete rows. The first row is always 10,
but the second row can vary past 10. I was wondering if there is quick
function or cleaner way of writing code to find the last row in Col.B that
contains an interior colorindex = 50? This is what I have.

Sub DeleteRows()

' find green line at end of quote sheet
lngLastRow = 10
Do Until Cells(lngLastRow, 2).Interior.ColorIndex = 50
lngLastRow = lngLastRow + 1
Loop

' delete rows (items) from quote sheet
Rows("10:" & lngLastRow).Delete Shift:=xlUp

End Sub
--
Cheers,
Ryan