VBA Code to Find Cells with a Particular color and copy those values to a new sheet

[cfries]

Hey all, so I used conditioning rules which I had fill the cell a certain color if it met that rule, now I want to use a VBA code to copy only the colored cells into a new worksheet. This is what I have come up with so far, am new to this so any help would be greatly appreciated.

Private Sub CommandButton2_Click()
Set MR = Range("C6:BQV6")
For Each cell In MR
If cell.Interior.Color = RGB(146, 208, 80) Then
Sheets("Results").Range("A4:A500") = cell.Range("C6:BQV6").Value
End If
Next
End Sub

 

[JustynaMK]

Hi & welcome to the forum.

Note that in order to retrieve a background colour created by Conditional Formatting you need to use .DisplayFormat.Interior.ColorIndex (or .Color) instead of .Interior.ColorIndex (or .Color). See screenshot below - using Interior.ColorIndex gives us -4142 which translates to "no fill", while .DisplayFormat.Interior.ColorIndex returns the proper colour index (43):

Now, back to the fun stuff.

In order to copy given set of cells from one sheet ("Sheet1") to the other ("Results") you can use the following code:

Sub foo()
    Dim MR As Excel.Range
    Dim rngCell As Excel.Range
    Dim rngCount As Long
    
    Set MR = Sheets("Sheet1").Range("A1:A6")
    
    rngCount = 1
    
    For Each rngCell In MR
        If rngCell.DisplayFormat.Interior.Color = RGB(146, 208, 80) Then
            Sheets("Results").Range("A" & rngCount) = rngCell.Value
            rngCount = rngCount + 1
        End If
    Next rngCell
End Sub

Change sheet names/ranges as required. Sheet1 is set up like that:

The final result will look like that:

I hope it helps!

 

[Peter_SSs]

Welcome to the MrExcel board!

.. I used conditioning rules which I had fill the cell a certain color if it met that rule, ...

What is the conditional formatting rule? It 'may' be that the rule itself could be used to achieve this result in a simpler fashion than by looking for the colour it generates.