CODEWITHSUNDEEP
excelvba
yathrakaaran
yathrakaaran

Reputation: 189

Copying based on cell color does not work using VBA in excel

I am trying to copy rows based on the cell color to a different sheet in excel. My code is below. But it's not copying. What am I doing wrong?

`

Sub Button1_Click()

a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

    If Worksheets("Sheet1").Cells(i, 2).Interior.Color = RGB(255, 0, 0) Then

        Worksheets("Sheet1").Rows(i).Copy

        Worksheets("Sheet2").Activate

        b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

        Worksheets("Sheet2").Cells(b + 1, 1).Select

        ActiveSheet.Paste

        Worksheets("Sheet1").Activate

    End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

End Sub

`

Upvotes: 0

Views: 165

Answers (1)

BigBen
BigBen

Reputation: 50007

From your previous question, I'm guessing you are working with conditional formatting.

In that case, you have to use Range.DisplayFormat.Interior.Color.

If Worksheets("Sheet1").Cells(i, 2).DisplayFormat.Interior.Color = RGB(255, 0, 0) Then

Note that you should also avoid Select and Activate.

Upvotes: 2

Related Questions