Can an excel macro handle colours ?
 

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

Can an excel macro handle colours ?
 

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" wrote in message
...
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

Can an excel macro handle colours ?
 

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" wrote in message
...
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" wrote in message
...
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

Can an excel macro handle colours ?
 

Roger,

Change the = 6 to < 6

--

HTH

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


"Roger Ottaway" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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