VBA Creating list of highlighted cells

Question

I am trying to create a consolidated list of highlighted cells that are on different tabs in my workbook. As I understand VBA is my best option. Logic would be something like:

Loop through all tabs and If cell = color index 4 then copy value and paste into a tab called 'highlightedcells'

Im as far as this currently

sub findhighlights
For Each ws In ActiveWorkbook.Worksheets
For Each Cell In ws.UsedRange.Cells
If Cell.Interior.ColorIndex = 4 Then
cell.value.copy

Any help would be welcomed.

You can't call `.copy` on a cell's `.Value`. There are examples here on SO of how to use value transfer: [this one](https://stackoverflow.com/questions/51528000/vba-paste-as-values-how-to), or how to use `.PasteSpecial xlPasteValues`: [this one](https://stackoverflow.com/questions/23937262/copy-paste-values-only-xlpastevalues). — BigBen, May 11 '22 at 13:16

score 2 · Accepted Answer · answered May 11 '22 at 17:16

Try this out:

Sub findhighlights()
    Dim wb As Workbook, ws As Worksheet, c As Range, wsList As Worksheet
    
    Set wb = ActiveWorkbook               'always use a specific workbook
    Set wsList = wb.Worksheets("Listing") 'listing goes here
    For Each ws In wb.Worksheets          'loop sheets
        If ws.Name <> wsList.Name Then    'skip the "Listing" sheet
            For Each c In ws.UsedRange.Cells
                If c.Interior.ColorIndex = 4 Then
                    'list info for this cell
                    With wsList.Cells(Rows.Count, "A").End(xlUp).Offset(1)
                        .Resize(1, 3).Value = Array(ws.Name, c.Address, c.Value)
                    End With
                End If
            Next c
        End If 'is not the Listing sheet
    Next ws
End Sub

VBA Creating list of highlighted cells

1 Answers1