short by colors

[linuxuser]

I have two reports that I run daily and everyday i go in to each report and manually enter colors into them. what I want to do is if there is a sertian color i want it to be removed from the second report so there is less on it for later. so if there is a cyan color I want to remove it.

an example is..

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--> <table style="width:108.4pt; margin-left:.8pt;border-collapse:collapse;mso-padding-alt:0in 0in 0in 0in" width="145" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height:12.75pt"> <td style="width:55.4pt;background:lime; padding:0in 0in 0in 0in;height:12.75pt" valign="bottom" width="74" nowrap="nowrap"> 06-29447-0
</td> <td style="width:53.0pt;padding:0in 0in 0in 0in; height:12.75pt" valign="bottom" width="71"> 06-28867-0
</td> </tr> <tr style="height:12.75pt"> <td style="background:aqua;padding:0in 0in 0in 0in; height:12.75pt" valign="bottom" nowrap="nowrap"> 06-33622-0
</td> <td style="padding:0in 0in 0in 0in;height:12.75pt" valign="bottom"> 06-28888-0
</td> </tr> <tr style="height:12.75pt"> <td style="background:#FFCC00;padding:0in 0in 0in 0in; height:12.75pt" valign="bottom" nowrap="nowrap"> 06-36112-0
</td> <td style="padding:0in 0in 0in 0in;height:12.75pt" valign="bottom"> 06-33622-0
</td> </tr> <tr style="height:12.75pt"> <td style="background:#99CCFF;padding:0in 0in 0in 0in; height:12.75pt" valign="bottom" nowrap="nowrap"> 06-36367-1
</td> <td style="padding:0in 0in 0in 0in;height:12.75pt" valign="bottom"> 06-36112-0
</td> </tr> <tr style="height:12.75pt"> <td style="background:lime;padding:0in 0in 0in 0in; height:12.75pt" valign="bottom" nowrap="nowrap"> 06-37671-0
</td> <td style="padding:0in 0in 0in 0in;height:12.75pt" valign="bottom"> 06-36552-0
</td> </tr> <tr style="height:12.75pt"> <td style="background:lime;padding:0in 0in 0in 0in; height:12.75pt" valign="bottom" nowrap="nowrap"> 07-00323-0
</td> <td style="padding:0in 0in 0in 0in;height:12.75pt" valign="bottom"> 06-40932-0
</td> </tr> <tr style="height:12.75pt"> <td style="background:lime;padding:0in 0in 0in 0in; height:12.75pt" valign="bottom" nowrap="nowrap"> 07-02663-0
</td> <td style="padding:0in 0in 0in 0in;height:12.75pt" valign="bottom"> 07-03052-0
</td> </tr> <tr style="height:12.75pt"> <td style="background:aqua;padding:0in 0in 0in 0in; height:12.75pt" valign="bottom" nowrap="nowrap"> 07-03267-0
</td> <td style="padding:0in 0in 0in 0in;height:12.75pt" valign="bottom"> 07-03267-0
</td> </tr> <tr style="height:12.75pt"> <td style="background:aqua;padding:0in 0in 0in 0in; height:12.75pt" valign="bottom" nowrap="nowrap"> 07-03279-0
</td> <td style="padding:0in 0in 0in 0in;height:12.75pt" valign="bottom"> 07-03279-0
</td> </tr> </tbody></table>

 

[Trevor G]

You have 2 with statements you can only use 1 at a time! Look at the amended code here.

Range("a1").Select
Application.FindFormat.Interior.Color = vbCyan
With Worksheets("Sheet1").Columns(1)
.Replace What:="", Replacement:="", SearchOrder:=xlByRows, SearchFormat:=True
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
Range("a1").Select
With Worksheets("Sheet2").Columns(1)
.Replace What:="", Replacement:="", SearchOrder:=xlByRows, SearchFormat:=True
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With