Excel VBA: Storing the Value of One Cell in a Variable if a Certain Value Exists in the Same Column

Question

I'm trying to determine how to store the value of a cell in a variable if a certain value exists in the same column. See the explanation below:

Example Table

So for example, I have a database storing whether an individual enjoys a certain fruit or not. What I want to happen is that for each person, going row by row in myRange (where myRange = "B" i & ":E" & i) if the cell is = Chr(13) & Chr(7) then store the value of the header in a variable (i.e. "Apple", "Grape", Banana", "Orange").

This is what I have right now as a snippet:

For i = 7 To iLastRow
    Set oCell = myRange.Find(What:=Chr(13) & Chr(7), LookIn:=xlValues, _ 
                LookAt:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, :=False, SearchFormat:=False)

    If oCell = Chr(13) & Chr(7) Then
        value = "DONT KNOW WHAT TO PUT HERE BUT IT WILL REFERENCE TO THE 
                ITEMS 'Apple', 'Grape','Banana', 'Orange' DEPENDING ON WHICH 
                COLUMN THE BLANK IS FOUND IN"
        sTemp = sTemp & "," & value
    Else
        Set oCell = Nothing
    End If
Next i
sTemp = Mid(sTemp, 2)

Answer 1

Here's one alternative using a loop in loop that gives a msgbox result as:

"Joe likes Orange, James likes Apple, Banana, Grape, John likes Apple, Banana, Orange, Jack likes Apple, Grape,"

Sub what()
Dim P As String: Dim X As String: Dim S As String
Dim i As Integer: Dim j As Integer: Dim iLastRow As Integer
With ThisWorkbook.Worksheets("Sheet3")
    iLastRow = 10
    For i = 7 To iLastRow
        P = .Cells(i, 1).Value2
        For j = 2 To 5
            If .Cells(i, j).Value = "Y" Then
                V = .Cells(6, j).Value
                S = S & V & ", "
            End If
        Next j
        X = P & " likes " & S & vbNewLine & X
        S = ""
    Next i
End With
MsgBox X
End Sub