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

Question

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

`

I'd guess the colour in the cell isn't `RGB(255,0,0)`. Have you stepped through your code to see what it's doing? and/or checked the colour is correct? (In immidiate window: `?Worksheets("Sheet1").Cells(i, 2).Interior.Color`) — Samuel Everson, Jun 02 '20 at 01:10
Conditional formatting? If Yes you need to use DisplayFormat.Interior.Color — Tim Williams, Jun 02 '20 at 01:19

score 2 · Accepted Answer · answered Jun 02 '20 at 01:18

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.

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

1 Answers1