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]

When you say remove it do you mean delete the row? Which version of Excel do you use as version 2007 and 2010 will allow you to filter by colours?

 

[Datsmart]

Take a look at VoG's suggestions here:
http://www.mrexcel.com/forum/showthread.php?t=533806

You can adjust the color number to fit your needs.

 

[linuxuser]

I'm using excel 2000

 

[Trevor G]

Give this a go.

Sub colour1()
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
End Sub

 

[linuxuser]

this is what I get. "Error 400"

-- removed inline image ---

 

[Trevor G]

Are you using Sheet1 or is there a different name for the sheet to check? If yes then change the sheet name in the code to the one it needs to check, and adjust the column number to check.

Sub colour1()
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
End Sub

 

[linuxuser]

does the macro have to refer to the sheet with the specific column (Column A) for the previous date and today's date.

 

[Trevor G]

You need to specify the sheet name you want it to remove the Cyan coloured row.

Have you looked yet at the link to VoG thread that John suggested, as it may work better for you.!!!!

 

[linuxuser]

yes I did fix that (it has been quite sometime since I used VB) to run as this so far.

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

My lack of using Vb is showing but I get a complie error: Expected End With pop up