compare two row ranges with same number of cells

Question

    Sub compareRange()
        If Worksheets("Sheet1").Range("A14:C14") = Worksheets("Sheet1").Range("A15:C15") Then
            MsgBox "Two Ranges are the same"
        End If
        'MsgBox "Two Ranges are the same"
    End Sub

Giving that A14:C14(1,2,3) and A15:C15(1,2,3) has same values, i am getting a type mismatch error. How am i suppose to compare two ranges like this?

Answer 1

You don't need VBA for that. Try this array formula:

=AND(EXACT(A14:C14,A15:C15))

After typing in the formula, use Ctrl+Shift+Enter to set it as an array formula.

It will return TRUE if the ranges are equal and FALSE if they aren't.

If you need to do this using VBA, try this:

Sub CompareTwoRanges()
    Dim compareRanges As Boolean
    compareRanges = ActiveSheet.Evaluate("=AND(EXACT(A14:C14,A15:C15))")
End Sub

EDIT: I don't like ActiveSheet, and I don't like the hard-coded ranges, so here is a more complete snippet:

Sub CompareTwoRanges()
    Dim wb As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range

    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets("Sheet1")
    Set ws2 = wb.Sheets("Sheet2")
    Set rng1 = ws1.Range("A14:C14")
    Set rng2 = ws2.Range("N3:P3")

    If rangesAreEqual(rng1, rng2, ws1, ws2) Then
        MsgBox "The ranges are equal."
    Else
        MsgBox "Sorry. The ranges are NOT equal."
    End If
End Sub

Function rangesAreEqual(rng1 As Range, rng2 As Range, _
    ws1 As Worksheet, ws2 As Worksheet) As Boolean
    ' booleans default to false
    ' verify ranges have same dimensions
    If rng1.Columns.Count <> rng2.Columns.Count Then Exit Function
    If rng1.Rows.Count <> rng2.Rows.Count Then Exit Function

    ' ranges are the same size. are their contents equal?
    rangesAreEqual = ws1.Evaluate("=AND(EXACT(" & ws1.Name & "!" & _
            rng1.Address & "," & ws2.Name & "!" & rng2.Address & "))")
End Function

Answer 2

You just can't do this. This is not how ranges work. You need to iterate through each cell in the ranges using some kind of for loop. You may want to add checks so that you spot cases where the number of cells is not equal (obviously the ranges are different if that is the case).