StrComp 2 Worksheets, Same WorkBook

[MR3]

 Sub CompareString ( )
aStr = ActiveSheet.Range("A1").Value
bStr = ActiveSheet.Range("A2").Value
If StrComp(aStr,bStr) = 0 Then
MsgBox "They match"
Else
MsgBox "They are not the same"
End If
End Sub

my code currently compares the string in cell A1 to the string in A2.
I would like to change the code so that it will compare all of the data in Column A Sheet1, with all of the Data in Column B Sheet2 and the highlight the like values in Sheet1.

 

[Trebor76]

Hi ndcruz21,

Try this (just change the cell formatting for found cell(s) to suit):

Option Explicit
Sub Macro1()

    Dim rngActiveCell As Range, _
        rngActiveRange As Range, _
        rngFoundCell As Range, _
        rngSearchRange As Range
        
    Set rngActiveRange = Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
    Set rngSearchRange = Sheets("Sheet2").Range("B1:B" & Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row)
    
    Application.ScreenUpdating = False
    
    For Each rngActiveCell In rngActiveRange
        If Len(rngActiveCell.Value) > 0 Then
            Set rngFoundCell = rngSearchRange.Find(What:=rngActiveCell.Value, LookIn:=xlValues, _
                                                   LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                   MatchCase:=True, SearchFormat:=False)
    
                'If the 'rngFoundCell' variable has been set, then...
                If Not rngFoundCell Is Nothing Then
                    '...colour the cell green (change to suit).
                    rngActiveCell.Interior.Color = RGB(0, 255, 0)
                End If
        End If
    
    Next rngActiveCell
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert

 

[MR3]

Hey Robert,
Thanks your code worked. VoG seems to have condensed the code down to a few lines.

 

[Trebor76]

VoG seems to have condensed the code down to a few lines

Not sure what you mean

If Peter has supplied another solution (from the posts I've seen), I'd use his.

If you mean the code in your initial post, this is fine for comparing a single cell, not a range for which you need.

In any case, I'm glad MrExcel was able to provide a solution

Robert