I have a chemical compounds database with 555 compounds in it, saved as Macros.xls in a worksheet named List . I need to make a macro that checks a new list in another workbook and highlights those compounds it found in the database.
I have started a tentative code structure and almost got it working, except for the Find method inside the Find function. Can somebody help me?
Sub CheckCompounds() MsgBox "Make sure compound list is A1:Ax" Dim temp As String Cells(1, 1).Select Do temp = ActiveCell.Value If Find(temp, "Macros", "List") = True Then Selection.Interior.ColorIndex = 36 Selection.Interior.Patter = xlSolid End If ActiveCell.Offset(1, 0).Select Loop While ActiveCell.Value > 0 End Sub Function Find(text As String, book As String, sheet As String) As Boolean Dim found As Boolean If Workbooks(book).Worksheets(sheet).Cells.Find(What:=text, _ LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=MatchCase, _ SearchFormat:=False).Value > 0 Then found = True End Function
I am trying to search the database for each string in the new list (which is vertical in the A column of a new sheet), and am considering any range returned by the Find() function with value > 0 to be a positive match. Apart from that, however, I am in dire need for help.
Can somebody come to my rescue?
PS. Both the database and the new workbook are in the same folder.