VBA code to compare the cell values of 2 sheets?

Question

I have 2 sheets - "Data Pulls" and "Hardcoded Values". The "Data Pulls" sheet contains a bunch of Bloomberg formulas to pull live data from the Bloomberg Terminal. The "Hardcoded Values" sheet contains that same data as values (rather than formulas). I am currently in the process of trying to code a button called "Highlight Differences", which when clicked, would essentially need to loop through the used range in the "Data Pulls" tab and compare it with the data in the "Hardcoded Values" tab. For it would need to compare if cell A8 in "Data Pulls" has the same data as cell A8 in "Hardcoded Values" and so on for every cell in the range. Should the two cells be different, the code should highlight the corresponding cell red in the "Data Pulls" tab. For example, if the Bloomberg formula in cell A8 in "Data Pulls" pulled "boy", and cell A8 in "Hardcoded Values" tab said "girl", cell A8 in "Data Pulls" would be highlighted red.

I have the current code, which gives me Run-time error '13': Type Mismatch. I assume this is because the cells in "Data Pulls" contain formulas, whereas the cells in "Hardcoded Values" contain values (text / numbers). I tried to incorporate a line in my code that stores the data derived from the formula as text, but to no avail. Could you please help me identify how to fix this?

 Sub HighlightDifferences()
 
 ' Define source and destination ranges 
 Dim srcRange As Range 
 Dim dstRange As Range
 
 Set srcRange = ThisWorkbook.Sheets("Data Pulls").Range("A8:W46") 
 Set dstRange = ThisWorkbook.Sheets("Hardcoded Values").Range("A8:W46")
 
 
 
 ' Store source data as text 
 Dim srcData As Variant 
 srcData = srcRange.Value2
 
 ' Loop through each cell 
    For i = 1 To srcRange.Rows.Count
     For j = 1 To srcRange.Columns.Count

         ' Compare values and highlight differences

         If UCase(CStr(srcData(i, j))) <> UCase(dstRange.Cells(i, j).Value) Then
             srcRange.Cells(i, j).Interior.ColorIndex = xlRed
         End If
     Next j 
 Next i
 
 End Sub

Answer 1

Highlight Differences

• Write the values of both ranges to arrays for faster processing.
• Convert both values to strings before comparing them to avoid run-time errors when comparing cells with errors or wrong highlighting e.g. when comparing the source string with the destination value.
• Use Union to combine the critical cells into a range and highlight it in one go.
• Don't forget to clear the existing highlights before applying the new ones.

Sub HighlightDifferences()
    
    Const SRC_SHEET_NAME As String = "Data Pulls"
    Const DST_SHEET_NAME As String = "Hardcoded Values"
    Const RANGE_ADDRESS As String = "A8:W46" ' at least 2 cells!
    
    Dim srg As Range, sData() As Variant, dData() As Variant
 
    With ThisWorkbook
        Set srg = .Sheets(SRC_SHEET_NAME).Range(RANGE_ADDRESS)
        sData = srg.Value
        dData = .Sheets(DST_SHEET_NAME).Range(RANGE_ADDRESS).Value
    End With
    
    Dim surg As Range, r As Long, c As Long
    
    For r = 1 To UBound(sData, 1)
        For c = 1 To UBound(sData, 2)
            If StrComp(CStr(sData(r, c)), CStr(dData(r, c)), _
                    vbTextCompare) <> 0 Then
                If surg Is Nothing Then
                    Set surg = srg.Cells(r, c)
                Else
                    Set surg = Union(surg, srg.Cells(r, c))
                End If
            End If
        Next c
    Next r
    
    srg.Interior.ColorIndex = xlNone
    
    If surg Is Nothing Then
        MsgBox "No differences found!", vbExclamation
    Else
        surg.Interior.Color = vbRed
        MsgBox "Differences highlighted.", vbInformation
    End If
 
End Sub