Thread: How can I create a list of data by cell color?

See attached.

Basic code - no error handling and set to use ONLY the shade of green you have used - will fail on any other shade of green.

It would best if you estalish a specific criteria for determining when a cell gets a green color and then use this same criteria in VBA.

Option Explicit

Sub Get_Cells_By_Color()

    Dim lastrow As Long, lastcol As Long
    Dim c As Range
    
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    lastcol = Cells(4, Columns.Count).End(xlToLeft).Column
    
    Application.ScreenUpdating = False
    
    '// Sheet references are sheet code name - change them as required
    '// cell ranges may need to be adjusted
    
    With Sheet1
        For Each c In .Range("A4", .Cells(lastrow, lastcol))
            If c.Interior.Color = 5296274 Then
                .Cells(c.Row, "E").Value = "green"
            End If
        Next c
        .AutoFilterMode = False
        .Range("E3:E" & lastrow).AutoFilter field:=1, Criteria1:="green"
        .Range("A3:C" & lastrow).SpecialCells(xlCellTypeVisible).Copy Sheet2.Range("A1")
        .AutoFilterMode = False
        .Range("E4:E" & lastrow).ClearContents
    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

There macro is assigned to a button on the sheet and the results are copied to sheet2.

Get Green Cells.xlsm