Identifying Matching Values Between 2 Sheets and Retaining Formatting When Sorting

[mrscottjr]

Good evening!

I have multiple sheets in my workbook. Sheets("Setup") and Sheets("Data") are the two sheets I'm looking to compare to find matching values. If any of the the values in Column C of the "Setup" sheet are found in Column E of the "Data" sheet, I'd like to highlight the cell green on the "Setup" sheet.

Unfortunately I'm also having difficulty with those highlighted matching cells retaining the color formatting when the "Setup" sheet is is sorted/filtered.

Any assistance would be great!

 

[AFPathfinder]

If the autofilter is not being removed from the Setup sheet, sorting should keep colors intact.

As for the matching, I think that's a VBA solution (someone might be able to hookup a VLOOKUP in conditional formatting or something).

Sub ValueMatching()
    Dim finalRow As Integer
    Dim i As Integer
    Dim x As Range
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To finalRow
        On Error Resume Next
        Set x = Sheets("Data").Columns(5).Find(What:=Cells(i, 3).Value, After:=Sheets("Data").Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)
        On Error GoTo 0
        
        If Not x Is Nothing Then
            Cells(i, 3).Interior.Color = 5880731
        End If
    Next i
End Sub

 

[mrscottjr]

Thanks for the response! Unfortunately I'm not getting any hits for a match when I know there are handful....

 

[AFPathfinder]

Since I can't see and test the results, there are two ways I'd suggest troubleshooting this. The first is to use message boxes to help you inspect what is going on, counting how many matches are found or just to ID the values it's looking at (maybe the references are off). The other is to step through the code and inspect the ones you know have a match. Maybe the matches aren't exact matches.

If I had to take a guess, I'd say the references are off on the Find function. I didn't put the sheet name on the What:= parameter, so maybe it's looking at column C on the Data sheet instead of the Setup sheet.