Okay, I figured. So how would I use a VBA to delete an entire row that
contains the word lab - the word may be inbetween other data such as
labuseonly or nonpt lab. Thanks!

"JLGWhiz" wrote:

Using conditional format to color the cell is different than using the
interior.colorindex method and requires more elaborate code to determine the
cells which are to be acted upon. The problem is that the only way to check
for the color is to check for the value of FormatConditions. This check
will show that the condition is equal to a colorindex but it does not show
if the condition is equal to true, so you can get false readings. I have
seen code that can be used, but I do not have it handy. A work around is to
use VBA to set the interior.colorindex and then Chip's code will work fine.
Or instead of checking for the color, check for the condition that sets the
color.



"ericaamousseau" wrote in message
...
Hello, I am trying to use this macro, but it does not seem to be working.
I
am really new at VBAs, so maybe I am doign something wrong, but my gut
tells
me that the reason this is not working is because the cells that are not
colorindex 4 are no fill because of a conditional format. so I have all
the
columns shaded green unless column G contains *lab* then the row is
formatted
to no fill. Can I not combine the VBA with the conditional format? If
not -
is there a VBA that I can put in that changes row color when G has *lab*?
thanks for the help I really appriciate it!

"Chip Pearson" wrote:

It isn't clear what you want. Do you want to test only cells in rows
10 to 15 and 100 to 200 and delete those rows that are not green? In
this case, use code like

Sub AAA()
Dim RowNum As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ErrH:
For RowNum = 200 To 100 Step -1
If Cells(RowNum, "A").Interior.ColorIndex < 4 Then
' not green -- delete.
Rows(RowNum).Delete shift:=xlShiftUp
End If
Next RowNum
For RowNum = 15 To 10 Step -1
If Cells(RowNum, "A").Interior.ColorIndex < 4 Then
' not green -- delete.
Rows(RowNum).Delete shift:=xlShiftUp
End If
Next RowNum
ErrH:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

If you want to delete ALL rows that are not green from 1 to 200, use

Sub BBB()
Dim RowNum As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
On Error GoTo ErrH:
For RowNum = 200 To 1 Step -1
If Cells(RowNum, "A").Interior.ColorIndex < 4 Then
Rows(RowNum).Delete shift:=xlShiftUp
End If
Next RowNum
ErrH:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


If you have something else in mind, you can modify either of the
following to meet your needs. The key here is that you want to
deletions to go from the bottom up -- highest row number to lowest row
number. Otherwise, you'll skip some rows.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 22 May 2009 13:13:01 -0700, Dan Thompson
wrote:

Hi there I have been looking for a simple way to delete rows that are
highlighted a certain color for example.

I have a worksheet where Rows 10 to 15 and 100 to 200 are interior
colorindex 4 What I would like to know is how to make vba delete all
rows
that are not colored green so that only the green ones remain at the top
of
the spreadsheet.

??
Dan Thompson



.