Changing Interior Colorindex in Macro if Match found

[AussieJac]

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").

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:

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

 

[ravishankar]

Insert The following code

Rows("x:x").Interior.ColorIndex = 6

replace x with the row number you want to highlight and 6 with any other number whose colour you like

Ravi