Need code edit..

[VBABEGINER]

What went wrong this code..?

For k = 2 To UsdRws 'UsdRws To 2 Step -1


If Cells(k, 1).Interior.Color = vbYellow Then
Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Rows(k).Delete
End If
Next k

 

[steve the fish]

If you step -1 you need to work upwards so

For k  = UsdRws to 2 Step -1

 

[VBABEGINER]

No, its not working. I want wherever, cell color is Yellow then cut and paste it into Sheet2. I think, it macro dont recognize the cell color.

If you step -1 you need to work upwards so

For k  = UsdRws to 2 Step -1

 

[steve the fish]

So its working. Its just not doing what you require. Im afraid yellow is meaningless to excel unless its the special vbYellow but that's only one shade of yellow. You need to use a shade that it recognises all the time or the logical test is going to fail as you have seen. If you do something like this it will work. Firstly run this:

Range("A1").Interior.Color = vbYellow

Click into cell A1. Go to home tab and double click format painter. Click into all of your yellow cells one by one. You can then rerun your macro. By the way how are you going to prevent duplication? Each time you run this you will get duplication if the cells are still yellow.

 

[VBABEGINER]

Apologies..
but not working my side.

Application.ScreenUpdating = False
UsdRws = Range("A" & Rows.Count).End(xlUp).Row
For k = UsdRws To 2 Step -1

If Cells(k, 1).Interior.Color = 65535 Then
Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Rows(k).Delete
End If
Next k
Application.ScreenUpdating = True

 

[steve the fish]

That works assuming you are on the correct sheet when you invoke the macro and your interior color of the cell is indeed vbYellow.

 

[VBABEGINER]

Is it possible for you to give correct code..

 

[steve the fish]

The code will run correctly provided the conditions in post 6 are adhered to. That bit has nothing to do with code.

 

[philwojo]

how are you coloring the cell, are you using Conditional Formatting (CF) or using cell fill?

 

[VBABEGINER]

With Conditional formatting...actually its long process Phil..

Requirement now is like, wherever the cell color is Yellow, that gets cut and paste it into Sheet2.
I am taking in sheet2 b'coz im not that much hard coder...

After taking on sheet2, im using this code..Actually my original code is this..

'copy in new workbook
Range("A2", Range("F500").End(xlDown)).Copy
Dim wb As Workbook
Set wb = Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2").Select


Columns("C:D").Select
Selection.EntireColumn.Delete




Dim UsdRws As Long, k As Long
Application.ScreenUpdating = False
UsdRws = Range("A" & Rows.Count).End(xlUp).Row
For k = UsdRws To 2 Step -1


'If Cells(k, 1).Interior.Color = 65535 Then
'Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
'Rows(k).Delete
'End If
'Next k


If Range("A" & k).Value Like "Exclusions" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "* Exclusions" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "Exclusions *" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "* Exclusions *" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete




ElseIf Range("A" & k).Value Like "Exclusion" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "* Exclusion" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "Exclusion *" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "* Exclusion *" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "Exclusion * " Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "Exclusion-*" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
    


ElseIf Range("A" & k).Value Like "Excl" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "* Excl" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "Excl *" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "* Excl *" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "Excl-* " Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
ElseIf Range("A" & k).Value Like "Excl-*" Then
    Rows(k).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Rows(k).Delete
End If


Next k
Application.ScreenUpdating = True


Range("A1:D1").Copy
Range("G1").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveSheet.UsedRange.Select
Selection.Copy
Sheets("sheet1").Select
Range("G2").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveSheet.UsedRange.Clear
Cells(1, 1).Select
Sheets("Sheet1").Select
ActiveWindow.Zoom = 90
Columns("E:F").Select
Selection.ColumnWidth = 4

but the requirement increasing day by day for "Excl" and "Exclusion" word...therefore, instead of mentioning this manual type I decide to use, conditional formatting wherever I find "Excl" and "Exclusion", make it Yellow color.

Now, I'm trying to get Yellow on Sheet1, Start from G2..

I think, my code is suffiecient to understand what I'm trying to do..

Thanks..Help appreciated pls..

how are you coloring the cell, are you using Conditional Formatting (CF) or using cell fill?