Copy any ROW that has RED background to Sheet2

  • Thread starter Thread starter RayD
  • Start date Start date
R

RayD

Good morning all,

I have a project where I have about 2000 rows (Sheet 1) that from day
to day, any number of the ROWS will change to RED background. It would
be great if I had a macro that would inspect the 2000 or so ROWS and
anything with a RED background would get copied to Sheet2. Any help
would be appreciated. -Thanks
 
try this.

Sub Macro1()
For Each c In [a1:a2000]
x = Sheets("sheet2").Cells(65536, 1).End(xlUp).Row + 1
If c.Interior.ColorIndex = 46 Then
c.EntireRow.Copy Sheets("sheet2").Cells(x, 1)
End If
Next
End Sub
 
Don,

I just give it a try and did not have any success. Out of all the 2000
ROWS the column the turns RED background is "H", not sure if this has
any effect. I will stay after it.



try this.

Sub Macro1()
For Each c In [a1:a2000]
x = Sheets("sheet2").Cells(65536, 1).End(xlUp).Row + 1
If c.Interior.ColorIndex = 46 Then
c.EntireRow.Copy Sheets("sheet2").Cells(x, 1)
End If
Next
End Sub

RayD said:
Good morning all,

I have a project where I have about 2000 rows (Sheet 1) that from day
to day, any number of the ROWS will change to RED background. It would
be great if I had a macro that would inspect the 2000 or so ROWS and
anything with a RED background would get copied to Sheet2. Any help
would be appreciated. -Thanks
Click to expand...
Click to expand...
 
Ray

If your colored rows are a result of Conditional Formatting you should be able
to copy these rows based upon the same parameters that produced the colors
through Conditional Formatting.

Otherwise see Chip Pearson's website for counting CF cells.

http://www.cpearson.com/excel/CFColors.htm

Here is the preface from this page.

"Unfortunately, the Color and ColorIndex properties of a Range don't return
the color of a cell that is displayed if Conditional formatting is applied to
the cell. Nor does it allow you to determine whether a conditional format is
currently in effect for a cell.
In order to determine these, you need code that will test the format
conditions. This page describes several VBA functions that will do this for
you."

If colors are the result of CF, Don's code will fail.
If manually colored Don's code should work.
Try colorindex = 3

Gord Dibben XL2002


Don,

I just give it a try and did not have any success. Out of all the 2000
ROWS the column the turns RED background is "H", not sure if this has
any effect. I will stay after it.



try this.

Sub Macro1()
For Each c In [a1:a2000]
x = Sheets("sheet2").Cells(65536, 1).End(xlUp).Row + 1
If c.Interior.ColorIndex = 46 Then
c.EntireRow.Copy Sheets("sheet2").Cells(x, 1)
End If
Next
End Sub

RayD said:
Good morning all,

I have a project where I have about 2000 rows (Sheet 1) that from day
to day, any number of the ROWS will change to RED background. It would
be great if I had a macro that would inspect the 2000 or so ROWS and
anything with a RED background would get copied to Sheet2. Any help
would be appreciated. -Thanks
Click to expand...
Click to expand...
Click to expand...

Gord Dibben XL2002
 
Gord,

You are 100% correct. I just ran into this issue. I guess I am back to
square 1 now. What I need to copy to Sheet2 is CF. -Thanks



Ray

If your colored rows are a result of Conditional Formatting you should be able
to copy these rows based upon the same parameters that produced the colors
through Conditional Formatting.

Otherwise see Chip Pearson's website for counting CF cells.

http://www.cpearson.com/excel/CFColors.htm

Here is the preface from this page.

"Unfortunately, the Color and ColorIndex properties of a Range don't return
the color of a cell that is displayed if Conditional formatting is applied to
the cell. Nor does it allow you to determine whether a conditional format is
currently in effect for a cell.
In order to determine these, you need code that will test the format
conditions. This page describes several VBA functions that will do this for
you."

If colors are the result of CF, Don's code will fail.
If manually colored Don's code should work.
Try colorindex = 3

Gord Dibben XL2002


Don,

I just give it a try and did not have any success. Out of all the 2000
ROWS the column the turns RED background is "H", not sure if this has
any effect. I will stay after it.



try this.

Sub Macro1()
For Each c In [a1:a2000]
x = Sheets("sheet2").Cells(65536, 1).End(xlUp).Row + 1
If c.Interior.ColorIndex = 46 Then
c.EntireRow.Copy Sheets("sheet2").Cells(x, 1)
End If
Next
End Sub

Good morning all,

I have a project where I have about 2000 rows (Sheet 1) that from day
to day, any number of the ROWS will change to RED background. It would
be great if I had a macro that would inspect the 2000 or so ROWS and
anything with a RED background would get copied to Sheet2. Any help
would be appreciated. -Thanks
Click to expand...
Click to expand...

Gord Dibben XL2002
Click to expand...
 
You would have had to change a1:a2000 to h1:h2000
but since it is conditional formatting, won't work anyway..

--
Don Guillett
SalesAid Software
(e-mail address removed)
RayD said:
Don,

I just give it a try and did not have any success. Out of all the 2000
ROWS the column the turns RED background is "H", not sure if this has
any effect. I will stay after it.



try this.

Sub Macro1()
For Each c In [a1:a2000]
x = Sheets("sheet2").Cells(65536, 1).End(xlUp).Row + 1
If c.Interior.ColorIndex = 46 Then
c.EntireRow.Copy Sheets("sheet2").Cells(x, 1)
End If
Next
End Sub

RayD said:
Good morning all,

I have a project where I have about 2000 rows (Sheet 1) that from day
to day, any number of the ROWS will change to RED background. It would
be great if I had a macro that would inspect the 2000 or so ROWS and
anything with a RED background would get copied to Sheet2. Any help
would be appreciated. -Thanks
Click to expand...
Click to expand...
Click to expand...
 
Yes I just found out a bit ago that the macro will not work on a
conditional formatted cell. bummer.... Well the next question would be
is it possible?? Here is the bad boy :) This works well just not on a
CF cell. -Thanks


Sub Macro1RED()
Sheets("Sheet2").Range("2:2000").Clear
Sheets("Sheet2").Range("2:2000").RowHeight = 12.75
For Each c In [H5:H2000]
x = Sheets("sheet2").Cells(65536, 1).End(xlUp).Row + 1
If c.Interior.ColorIndex = 3 Then
c.EntireRow.Copy Sheets("sheet2").Cells(x, 1)
End If
Next
End Sub
 
Back
Top