Removal red rows in excel

[Guest]

Lets say I have an excel spreadsheet file that consists of a number rows with
text, each of which is painted in either of two colors: green or red. I want
to remove those rows that colored in red. how can I do this?

 

[Norman Jones]

Hi Jimmy,

Try:

'================>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim rCell As Range
Dim delRng As Range

Set WB = ActiveWorkbook '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

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

Set rng = SH.Range("A1:A" & LastRow)

For Each rCell In rng.Cells
With rCell
If .Interior.ColorIndex = 3 Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
End With
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If


End Sub
'<<================