VBA Code for cell values comparison between two sheets in a workbook

[Excel_starter]

Hi All,
I am new to Excel and I have been assigned with a task to compare cell values on two different sheets and identify the differences.
Below is the current scenario.
I have two worksheets in a book. WS1 has values cells (A1 to A45) and WS2 has values on cells A1 to AR1.
I need to compare each cell value on ws2 with values in WS1 and highlight the cell on WS2 if it does not exists on WS1.
Can you guys please help?
Thank you so much in advance.

 

[Joe4]

Welcome to the Board!

You should be able to do this pretty easily without having to use VBA.
Just use the COUNTIF function within Conditional Formatting, counting how many times the value in a cell on WS2 appears on WS1.
If it is more than zero, then you want to highlight it.

So your formula would look something like (for cell A1):

=COUNTIF(Sheet1!$A$1:$A$45,Sheet2!A1)>0

Just select the whole range you want to apply it to (A1:AR1), enter the formula above, and select your desired highlighting color.

 

[OaklandJim]

I wrote some VBA to do this. Possibly way too complicated or unneeded but I wanted to address possible scenarios: 1) the range of cells to "look for" and those to "look at" may change and 2) the name of the worksheet to look in and the one to look at may also change. With code as written this is easy to change. Also, the code removes existing highlighting for the next iteration.

Sub HighlightValuesFound()

'   Worksheet containing values to "look for".
    Dim wsLookFor As Worksheet
    
'   Worksheet containing values to "look at".
    Dim wsLookAt As Worksheet

'   Look for values that are in this range...
    Dim rLookForValuesRange As Range
    
'   ...within this range.
    Dim rLookAtValuesRange As Range
    
'   Range object for looping lookup values range.
    Dim rLookForValueCell As Range
    
'   First data cell containing values to "look for".
    Dim rLookForValuesRangeAnchor As Range

'   First data cell containing values to "look in".
    Dim rLookAtValuesRangeAnchor As Range
    
'   Count of how many "look for" cells to process.
    Dim iCountCellsLookFor As Long

'   Count of how many "look at" cells to process.
    Dim iCountCellsLookAt As Long
    
'   Flag indicating if the "look for" value is found in the "look at" range.
    Dim bFound As Boolean
    
'   Point workshet object to worksheet containing values to "look for".
    Set wsLookFor = ThisWorkbook.Worksheets("Sheet2")
    
'   Point workshet object to worksheet containing values to "look at".
    Set wsLookAt = ThisWorkbook.Worksheets("Sheet1")
    
'   First (leftmost) cell containing data to "look for".
    Set rLookForValuesRangeAnchor = wsLookFor.Range("A1")
    
'   First (topmost) cell containing data to "look at".
    Set rLookAtValuesRangeAnchor = wsLookAt.Range("A1")
    
'   Get count of values to look for.
    iCountCellsLookFor = rLookForValuesRangeAnchor.Cells(1, Columns.Count).End(xlToLeft).Column
    iCountCellsLookFor = iCountCellsLookFor - rLookForValuesRangeAnchor.Column
    
'   Get count of values to look at.
    iCountCellsLookAt = rLookAtValuesRangeAnchor.Cells(Rows.Count, 1).End(xlUp).Row + 1
    iCountCellsLookAt = iCountCellsLookAt - rLookAtValuesRangeAnchor.Row + 1

'   Range containing values to look for.
    Set rLookForValuesRange = rLookForValuesRangeAnchor.Resize(1, iCountCellsLookFor)
    
'   Range containing values to look at.
    Set rLookAtValuesRange = rLookAtValuesRangeAnchor.Resize(iCountCellsLookAt, 1)
    
'   Get rid of existing highlights in values to look for range.
    With rLookForValuesRange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
'   Loop through values to find (range).
    For Each rLookForValueCell In rLookForValuesRange
    
'       If value in range to "look in" is found in range to "look at"
'       then bFound = TRUE. Use function named IsValueInRange to do that.
        bFound = IsValueInRange(rLookForValueCell.Value, rLookAtValuesRange)

        If bFound _
         Then
            
'           If value to "look for" is in the range to "look at" then highlight it.
            With rLookForValueCell.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        
        End If
    
    Next rLookForValueCell

End Sub
'

Function IsValueInRange(pvValueToLookFor As Variant, prValuesToLookAt As Range) As Boolean

    Dim rCell As Range
    
    IsValueInRange = False
    
    For Each rCell In prValuesToLookAt
    
        If rCell.Value = pvValueToLookFor _
         Then
            
            IsValueInRange = True
            
            Exit For
            
        End If
    
    Next rCell

End Function