deleting rows macro

I have around 3000 rows of data
If the background color of the cell in Col B is light blue (in the
color palette, col 7 , row 4) , then it should delete the row

thank you


---
Message posted from http://www.ExcelForum.com/

Hi
try the following macro:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "B").interior.colorindex= 42 then
Rows(row_index).delete
End If
Next
Application.ScreenUpdating = True
End Sub


You may have to adapt the line
If Cells(row_index, "B").interior.colorindex= 42 then

to your specific colorindex (as my color palette looks differently and
I'm not sure which color you have chosen). To get your colorindex try
the following:
- format a cell with this color and select this cell
- open the VBA editor and in the immediate window enter
?Activecell.interior.colorindex


--
Regards
Frank Kabel
Frankfurt, Germany


I have around 3000 rows of data
If the background color of the cell in Col B is light blue (in the
color palette, col 7 , row 4) , then it should delete the row

thank you


---
Message posted from http://www.ExcelForum.com/

fullymooned

try this code

You may have to change the ColorIndex number as my color palate layout
appears to be different to yours.





Sub DelRows()
Dim lRow As Long

Application.ScreenUpdating = False

For lRow = Cells.Find(what:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row _
To 1 Step -1

If Cells(lRow, "b").Interior.ColorIndex = 34 Then
Rows(lRow).Delete
End If
Next lRow

Application.ScreenUpdating = True
End Sub


---
Message posted from http://www.ExcelForum.com/