Cut and Paste entire Row to another sheet

[trevor2524]

Hello, I'm trying to figure out how to cut and paste an entire row to a certain sheet based on column I in sheet 1. If it contains the word newspaper it will then be transferred to the newspaper sheet. I have TV and radio as other categories. It will only search for rows that are not colored red in sheet 1 based on column A. After it cuts the rows it will then delete the row and move everything up one and then check the next available cell. Any help on this would be greatly appreciated.

 

[JLGWhiz]

Hello, I'm trying to figure out how to cut and paste an entire row to a certain sheet based on column I in sheet 1. If it contains the word newspaper it will then be transferred to the newspaper sheet. I have TV and radio as other categories. It will only search for rows that are not colored red in sheet 1 based on column A. After it cuts the rows it will then delete the row and move everything up one and then check the next available cell. Any help on this would be greatly appreciated.

If the cell fill color is by conditional format rather than regular fill, then this code fails.

Sub news()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Sheets("newspaper") 'Edit sheet name
lr = sh1.Cells(Rows.Count, "I").End(xlUp).Row
Set rng = sh1.Range("I2:I" & lr)
    For Each c In rng
        If LCase(c.Value) = "newspaper" And sh1.Range("A" & c.Row).Interior.ColorIndex <> 3 Then
            c.EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
            Rows(c.Row).Delete
        End If
    Next
End Sub

 

[trevor2524]

It's done by regular fill through another macro. Which portion of your code can I change to make it work? Thanks again.

 

[JLGWhiz]

It's done by regular fill through another macro. Which portion of your code can I change to make it work? Thanks again.

If it is regular fill, then it should work as is.

Only conditional Formatting fill would cause a problem, because colors per se are not detectable if established by CF. For whatever reason, they are on a different level in the application. Chip Pearson has an article about it on his website. The CF cells have to be detected by the same criteria that causes them to set the colors.