Can an excel macro handle colours ?

[Roger Ottaway]

I have Excel 2002sp3 on WinXP. Every week I get an excel spreadsheet listing
companies that have gone bankrupt. The company data is detailed across a
number of columns, with each company taking up one row and being listed
alphabetically.The compilation of the list starts on 1 January, continues
for the year, and is added to (updated) each week. The new bankrupt
companies, the new rows, are highlighted in yellow.



I need to print out just the new rows, the rows that are highlighted yellow.
Is there any way I can have a macro test the row to see if it is yellow, and
if NOT delete, so that I am left with a spreadsheet of only yellow rows
which I can then print. Currently I do this manually, how can I do it with a
macro ?



with thanks … Roger

 

[Bob Phillips]

Here is some code

Sub DeleteRows()
Dim cLastRow As Long
Dim i As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = cLastRow To 1 Step -1
If Cells(i, "A").Interior.ColorIndex = 6 Then
Cells(i, "A").EntireRow.Delete
End If
Next i

End Sub


--

HTH

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

 

[Roger Ottaway]

Thanks for your help, it works great ... but it does the opposite of what I
want. The macro deletes the yellow lines ... I want it to delete the lines
THAT ARE NOT yellow so that I only have yellow lines left !!

thanks .. Roger

 

[Bob Phillips]

Roger,

Change the = 6 to <> 6

--

HTH

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