VBA - generate a new table based on conditional values in an existing table

g00ber 

An alternative. It assumes that you already created the target sheet, with column headers in A1:C1.

Sub UpdateOutput()
    Const red = 192     ' Red color used - check your sheet
    Dim ws As Worksheet ' Source sheet
    Dim s As Long       ' Row number on source sheet
    Dim m As Long       ' Max row number on source sheet
    Dim c As Long       ' Column number on source sheet
    Dim n As Long       ' Max column number on source sheet
    Dim r As Long       ' Count of red cells in column on source sheet
    Dim p As String     ' Person names on source sheet
    Dim wt As Worksheet ' Target sheet
    Dim t As Long       ' Row number on target sheet
    Application.ScreenUpdating = False
    Set ws = Worksheets("Sheet1")
    m = ws.Cells(1, 1).End(xlDown).Row
    n = ws.Cells(1, 1).End(xlToRight).Column
    Set wt = Worksheets("Sheet2")
    wt.Range("A2:C1000").Clear
    t = 1
    For c = 2 To n
        r = 0
        p = ""
        For s = 2 To m
            If ws.Cells(s, c).Interior.Color = red Then
                r = r + 1
                p = p & vbLf & ws.Cells(s, 1).Value
            End If
        Next s
        If r > 0 Then
            t = t + 1
            wt.Cells(t, 1).Value = ws.Cells(1, c).Value
            wt.Cells(t, 2).Value = r
            wt.Cells(t, 3).Value = Mid(p, 2)
        End If
    Next c
    wt.Cells(2, 1).Resize(t - 1).NumberFormat = "mmm-yy"
    wt.Cells(2, 3).Resize(t - 1).WrapText = True
    Application.ScreenUpdating = True
End Sub