Macro/Loop If Statement Help -delete the row with the specific te

I am trying to find all the cells in my worksheet that have "AB" in column
'I' and the AB is highlighted with an interior color = 6 .... then delete the
row .

Here is what I have so far.

FIRSTROW = 1
Do While FIRSTROW > 0
FIRSTROW = Application.Match("AB", Range("i:i"), 0)
If Range("a" & FIRSTROW).Interior.ColorIndex = 6 Then
Rows(FIRSTROW & ":" & FIRSTROW).Select
Selection.Delete
End If

'this is the part that is not working. What is happening is that when their
is no match the code bombs so I tried to put an if statement in to change the
firstrow to 0 if their is no match. But the if statement is not working.

If FIRSTROW = Application.Match("AB", Range("i:i"), 0) Is Nothing Then
FIRSTROW = 0
End If
Loop

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim j As Long

iLastRow = Cells(Rows.Count, "I").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "I").Value = "AB" And _
Rows(i).Delete
End If
Next i
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bricktop" <[email protected]> wrote in message
news:[email protected]...
> I am trying to find all the cells in my worksheet that have "AB" in column
> 'I' and the AB is highlighted with an interior color = 6 .... then delete
the
> row .
>
> Here is what I have so far.
>
> FIRSTROW = 1
> Do While FIRSTROW > 0
> FIRSTROW = Application.Match("AB", Range("i:i"), 0)
> If Range("a" & FIRSTROW).Interior.ColorIndex = 6 Then
> Rows(FIRSTROW & ":" & FIRSTROW).Select
> Selection.Delete
> End If
>
> 'this is the part that is not working. What is happening is that when
their
> is no match the code bombs so I tried to put an if statement in to change
the
> firstrow to 0 if their is no match. But the if statement is not working.
>
> If FIRSTROW = Application.Match("AB", Range("i:i"), 0) Is Nothing Then
> FIRSTROW = 0
> End If
> Loop
>

try this:

a=1
while a < numberofrows
If cells(a,numcol).Interior.ColorIndex = 6 and cells(a,numcol)="AB" Then
rows(a).delete
a=a+1
else if
a=a+1
end if
wend

remember to change numcol to the number of the column were your AB's are and
numberofrows to the number of the last row used

"Bricktop" wrote:

> I am trying to find all the cells in my worksheet that have "AB" in column
> 'I' and the AB is highlighted with an interior color = 6 .... then delete the
> row .
>
> Here is what I have so far.
>
> FIRSTROW = 1
> Do While FIRSTROW > 0
> FIRSTROW = Application.Match("AB", Range("i:i"), 0)
> If Range("a" & FIRSTROW).Interior.ColorIndex = 6 Then
> Rows(FIRSTROW & ":" & FIRSTROW).Select
> Selection.Delete
> End If
>
> 'this is the part that is not working. What is happening is that when their
> is no match the code bombs so I tried to put an if statement in to change the
> firstrow to 0 if their is no match. But the if statement is not working.
>
> If FIRSTROW = Application.Match("AB", Range("i:i"), 0) Is Nothing Then
> FIRSTROW = 0
> End If
> Loop
>

Bob - This Worked Fantastic. I didn't know you could use an AND statement
with the if too. Just one more question...please. I have more than one that
has to go thru the loop and I was wondering if I can Combine them. Here is
what I have:
'Remove AB Codes that are ETA Pending

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "I").End(xlUp).row
For i = iLastRow To 1 Step -1
If Cells(i, "I").Value = "AB" And Cells(i, "I").Interior.ColorIndex
= 6 Then
Rows(i).Delete
End If
Next i

iLastRow = Cells(Rows.Count, "I").End(xlUp).row
For i = iLastRow To 1 Step -1

If Cells(i, "I").Value = "ABPEND" And Cells(i,
"I").Interior.ColorIndex = xlNone Then
Rows(i).Delete
End If
Next i


"Bob Phillips" wrote:

> Sub Test()
> Dim iLastRow As Long
> Dim i As Long
> Dim j As Long
>
> iLastRow = Cells(Rows.Count, "I").End(xlUp).Row
> For i = iLastRow To 1 Step -1
> If Cells(i, "I").Value = "AB" And _
> Rows(i).Delete
> End If
> Next i
> End Sub
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Bricktop" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to find all the cells in my worksheet that have "AB" in column
> > 'I' and the AB is highlighted with an interior color = 6 .... then delete
> the
> > row .
> >
> > Here is what I have so far.
> >
> > FIRSTROW = 1
> > Do While FIRSTROW > 0
> > FIRSTROW = Application.Match("AB", Range("i:i"), 0)
> > If Range("a" & FIRSTROW).Interior.ColorIndex = 6 Then
> > Rows(FIRSTROW & ":" & FIRSTROW).Select
> > Selection.Delete
> > End If
> >
> > 'this is the part that is not working. What is happening is that when
> their
> > is no match the code bombs so I tried to put an if statement in to change
> the
> > firstrow to 0 if their is no match. But the if statement is not working.
> >
> > If FIRSTROW = Application.Match("AB", Range("i:i"), 0) Is Nothing Then
> > FIRSTROW = 0
> > End If
> > Loop
> >
>
>
>

You can have Or as well

'Remove AB Codes that are ETA Pending

Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "I").End(xlUp).row
For i = iLastRow To 1 Step -1
If (Cells(i, "I").Value = "AB" And _
Cells(i, "I").Interior.ColorIndex = 6) Or _
(Cells(i, "I").Value = "ABPEND" And _
Cells(i, "I").Interior.ColorIndex = xlNone) Then
Rows(i).Delete
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bricktop" <[email protected]> wrote in message
news:[email protected]...
> Bob - This Worked Fantastic. I didn't know you could use an AND statement
> with the if too. Just one more question...please. I have more than one
that
> has to go thru the loop and I was wondering if I can Combine them. Here
is
> what I have:
> 'Remove AB Codes that are ETA Pending
>
> Dim iLastRow As Long
> Dim i As Long
>
> iLastRow = Cells(Rows.Count, "I").End(xlUp).row
> For i = iLastRow To 1 Step -1
> If Cells(i, "I").Value = "AB" And Cells(i,
"I").Interior.ColorIndex
> = 6 Then
> Rows(i).Delete
> End If
> Next i
>
> iLastRow = Cells(Rows.Count, "I").End(xlUp).row
> For i = iLastRow To 1 Step -1
>
> If Cells(i, "I").Value = "ABPEND" And Cells(i,
> "I").Interior.ColorIndex = xlNone Then
> Rows(i).Delete
> End If
> Next i
>
>
> "Bob Phillips" wrote:
>
> > Sub Test()
> > Dim iLastRow As Long
> > Dim i As Long
> > Dim j As Long
> >
> > iLastRow = Cells(Rows.Count, "I").End(xlUp).Row
> > For i = iLastRow To 1 Step -1
> > If Cells(i, "I").Value = "AB" And _
> > Rows(i).Delete
> > End If
> > Next i
> > End Sub
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Bricktop" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am trying to find all the cells in my worksheet that have "AB" in
column
> > > 'I' and the AB is highlighted with an interior color = 6 .... then
delete
> > the
> > > row .
> > >
> > > Here is what I have so far.
> > >
> > > FIRSTROW = 1
> > > Do While FIRSTROW > 0
> > > FIRSTROW = Application.Match("AB", Range("i:i"), 0)
> > > If Range("a" & FIRSTROW).Interior.ColorIndex = 6 Then
> > > Rows(FIRSTROW & ":" & FIRSTROW).Select
> > > Selection.Delete
> > > End If
> > >
> > > 'this is the part that is not working. What is happening is that when
> > their
> > > is no match the code bombs so I tried to put an if statement in to
change
> > the
> > > firstrow to 0 if their is no match. But the if statement is not
working.
> > >
> > > If FIRSTROW = Application.Match("AB", Range("i:i"), 0) Is Nothing
Then
> > > FIRSTROW = 0
> > > End If
> > > Loop
> > >
> >
> >
> >

I tried the following:
Range("U2:U" & LASTROW).FormulaR1C1 =
"=IF(SEARCH(""pen"",RC[-2])>0,2,IF(SEARCH(""can"",RC[-2])>0,2,IF(SEARCH(""hol"",RC[-2])>0,2,1)))"
and this doesn't work either. Can you not do a multiple search?? I tried
just putting one with both of the if statements and that doesn't work either.
Any other suggestions?


"Bricktop" wrote:

> I am trying to find all the cells in my worksheet that have "AB" in column
> 'I' and the AB is highlighted with an interior color = 6 .... then delete the
> row .
>
> Here is what I have so far.
>
> FIRSTROW = 1
> Do While FIRSTROW > 0
> FIRSTROW = Application.Match("AB", Range("i:i"), 0)
> If Range("a" & FIRSTROW).Interior.ColorIndex = 6 Then
> Rows(FIRSTROW & ":" & FIRSTROW).Select
> Selection.Delete
> End If
>
> 'this is the part that is not working. What is happening is that when their
> is no match the code bombs so I tried to put an if statement in to change the
> firstrow to 0 if their is no match. But the if statement is not working.
>
> If FIRSTROW = Application.Match("AB", Range("i:i"), 0) Is Nothing Then
> FIRSTROW = 0
> End If
> Loop
>