Greetings everyone, I'm self-taught and I know my code is garbage but it works. Basically I have a spreadsheet with filenames in a row, and keywords on the row to it's right. I created a working UserForm that will color the lines where the searched text was found. The user can enter up to 3 words to search and the lines will be colored if either of these words are found. I also added the blank textbox check to prevent coloring every empty line. Lastly, I have a working reset button to return everything to their default colors.
So I'm pretty happy about all of this as it's my first time playing with code.
However, it would be better if instead of coloring the lines where the text is found, if the code would start outputting the filenames somewhere for all the positive search results.
In column G I have my filenames, and column H I have thekeywords that get searched.
I was thinking about picking a random case left of my data, say B3, and list the filesnames starting from there for all the positive matches. That means if 3 matches were found, B3, B4 and B5 would have the corresponding filenames in them.
Here is my code so far, I know I probably could've used a "for i = 1 to 3" loop but I didn't even know the right syntax in this programming language.
![]()
Private Sub CommandButton1_Click() Dim SrchRng3 As Range Dim c3 As Range, f As String Dim Recherche1, Recherche2, Recherche3 As String Recherche1 = TextBox1.Text Recherche2 = TextBox2.Text Recherche3 = TextBox3.Text If Not Recherche1 = "" Then Set SrchRng3 = ActiveSheet.Range("H1", ActiveSheet.Range("H100").End(xlUp)) Set c3 = SrchRng3.Find(Recherche1, LookIn:=xlValues) If Not c3 Is Nothing Then f = c3.Address Do With ActiveSheet.Range("G" & c3.Row & ":H" & c3.Row) .Font.ColorIndex = 2 .Interior.ColorIndex = 53 End With Set c3 = SrchRng3.FindNext(c3) Loop While c3.Address <> f End If End If If Not Recherche2 = "" Then Set SrchRng3 = ActiveSheet.Range("H1", ActiveSheet.Range("H100").End(xlUp)) Set c3 = SrchRng3.Find(Recherche2, LookIn:=xlValues) If Not c3 Is Nothing Then f = c3.Address Do With ActiveSheet.Range("G" & c3.Row & ":H" & c3.Row) .Font.ColorIndex = 2 .Interior.ColorIndex = 53 End With Set c3 = SrchRng3.FindNext(c3) Loop While c3.Address <> f End If End If If Not Recherche3 = "" Then Set SrchRng3 = ActiveSheet.Range("H1", ActiveSheet.Range("H100").End(xlUp)) Set c3 = SrchRng3.Find(Recherche3, LookIn:=xlValues) If Not c3 Is Nothing Then f = c3.Address Do With ActiveSheet.Range("G" & c3.Row & ":H" & c3.Row) .Font.ColorIndex = 2 .Interior.ColorIndex = 53 End With Set c3 = SrchRng3.FindNext(c3) Loop While c3.Address <> f End If End If End Sub
Bookmarks