Changing Interior Colorindex in Macro if Match found

AussieJac

Board Regular
Joined
Jul 7, 2005
Messages
57
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:

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

Forum statistics

Threads
1,136,878
Messages
5,678,324
Members
419,756
Latest member
vincent86kapelski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top