If Color than display content

[EdGl]

hello everyone,

I am new to this community and also new to Excel and looking for a solution that may be easy for some of you.

I would like to display data based on their color. So there is a search based on a certain word, when it is found the row is displayed in blue.
Now in another sheet I would like to display only the coloured rows.

As I am not a native English speaker and certainly not a native Excel speaker, I hope my problem has become clear.
Perhaps a screenshot will make it clear

Thank you for your help!!!

Edda

 

[Takae]

Which cell has the particular word? Or is it immutable?
Where is the column(s) to search?

 

[EdGl]

Hey Takae,
thank you for your replay.

At the top of the page there is an option where you can search for the corresponding word (D2).
There I have a conditional formatting.

=WENN(Aus="";0;SUCHEN(Aus;$B2&$C2&$D2&$E2&$F2&$G2))

This colours all lines blue when the corresponding search word is found.

Now I want only the results of this search on the first sheet of my Excel table. So a list of the blue data. Do you think there is a solution?
Thank you,
Edda

 

[Takae]

Which column do you want to search?
And what line is the header of the first tab?

 

[Takae]

Assuming are D2 has the word, the searching column is only columnD, the data has header on row3, last column of the data is columnJ, next sheet has header on row1.
Please paste the code below into the sheet module of first sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D2")) Is Nothing Then
        Exit Sub
    Else
        Range("A3").AutoFilter
        Range("A3:J3").AutoFilter field:=4, Criteria1:=Target
        If WorksheetFunction.Subtotal(3, Range("A:A")) > 1 Then
            Range(Range("A4"), Cells(Rows.Count, 10).End(xlUp)).Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            Sheets("Sheet2").Activate
        End If
    End If
End Sub