I have this macro working beautifully thanks to a certain member of this board (Macropheliac you Rule!) BUT as always, I want more.
I have been playing around with changing the interior color index of the entire row when the macro finds a match in the database ("Data List amended for DOD").
Now I want it to highlight the row in Workbook "Data List amended for DOD" when match is found.
Working code:
I know it must be simple because it is driving me insane!
Jac
I have been playing around with changing the interior color index of the entire row when the macro finds a match in the database ("Data List amended for DOD").
The assumptions:
-Workbook "Claimed Matching Data" / Worksheet "M-Matching Data" / Column C loaded with numbers.
-Workbook "Data List amended for DOD" / Worksheets "List A", List B, "List C", "List D", "List E", "List F" / Column B loaded with numbers.
This procedure should:
-Open Workbook "Data List amended for DOD"
-Search Worksheets "List A", List B, "List C", "List D", "List E", "List F" / Column B for cells matching cells in Workbook "Claimed Matching Data" / Worksheet "M-Matching Data" / Column C
-Place the Value "Found in Database" in Column M in Workbook "Claimed Matching Data" / Worksheet "M-Matching Data" / Column C for which a match is found.
-Copies data from one spreadsheet to the other to provide checking procedure
Now I want it to highlight the row in Workbook "Data List amended for DOD" when match is found.
Working code:
Code:
Sub Test2()
' Open Data Base file to facilitate matching process.
' Check for matching data
Dim currentfile As String
Dim WS As Worksheet
Dim R As Range
Dim Myvalue As String
Dim Myrange As Range
Dim Tcell As Range
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open Filename:=ThisWorkbook.Path & "\Data List amended for DOD.xls"
ThisWorkbook.Sheets("M - Matching Data").Activate
For Each Tcell In ActiveSheet.Range("C3:C" & ActiveSheet.Range("C65536").End(xlUp).Row)
Set Myrange = Tcell.Offset(0, 1)
Myvalue = Tcell.Value
For Each WS In Workbooks("Data List amended for DOD.xls").Sheets
Select Case WS.Name
Case "List A", "List B", "List C", "List D", "List E", "List F", "List G", "List H", "List I", "List J", "List K", "List L", "List M", "List N", "List O", "List P", "List Q", "List R", "List S", "List T", "List U", "List V", "List W", "List X Y Z"
With WS.Range("b2:b3000")
Set R = .Find(Myvalue, LookIn:=xlValues, lookat:=xlWhole)
If Not R Is Nothing Then
Myrange.Offset(0, 9).Value = "Found in Data Base"
Myrange.Offset(0, 10).Value = R.Value 'copies contract number from database
Myrange.Offset(0, 11).Value = R.Offset(0, 2).Value 'copies Date of Birth from database
Myrange.Offset(0, 12).Value = R.Offset(0, 3).Value 'copies Name from database
Myrange.Offset(0, 13).Value = R.Offset(0, 4).Value 'copies Name from database
Myrange.Offset(0, 14).Value = R.Offset(0, 5).Value 'copies Name from database
R.Offset(0, 15).Value = Myrange.Offset(0, 4).Value 'copies Claim No TO database
R.Offset(0, 16).Value = Myrange.Offset(0, 5).Value'copies Amount Paid TO database
R.Offset(0, 17).Value = Myrange.Offset(0, 6).Value'copies Type of Claim TO database
End If
End With
End Select
Next WS
Next Tcell
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
I know it must be simple because it is driving me insane!
Jac