How do I use VBA to make a list of all cells rows with red cells? thank you

Question

How do i make excel compile a list of every row in a sheet with a red cell using VBA Thanks

Answer 1

Hi, Here's the VBA Code-

Sub ListRowsWithRedCells()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim redRows As Collection
    Dim outputRange As Range
    Dim outputRow As Range
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("YourSheetName") ' Replace "YourSheetName" with the actual sheet name
    
    ' Set the range to check (adjust as needed)
    Set rng = ws.UsedRange
    
    ' Initialize collection to store rows with red cells
    Set redRows = New Collection
    
    ' Loop through each row in the specified range
    For Each cell In rng.Rows
        ' Check if any cell in the row has a red font or fill color
        If HasRedColor(cell) Then
            ' Add the entire row to the collection
            redRows.Add cell.EntireRow
        End If
    Next cell
    
    ' Create a new worksheet to output the results
    Set outputRange = Worksheets.Add.Range("A1")
    
    ' Loop through the collection and copy rows to the new worksheet
    For Each outputRow In redRows
        outputRow.Copy outputRange
        Set outputRange = outputRange.Offset(1)
    Next outputRow
End Sub

Function HasRedColor(rng As Range) As Boolean
    ' Check if any cell in the range has a red font or fill color
    Dim cell As Range
    For Each cell In rng.Cells
        If cell.Font.Color = RGB(255, 0, 0) Or cell.Interior.Color = RGB(255, 0, 0) Then
            HasRedColor = True
            Exit Function
        End If
    Next cell
    HasRedColor = False
End Function

The code assumes the red color is represented by RGB(255, 0, 0). If your red color differs, adjust the RGB values accordingly. Regards, Tanay