Type mismatch error, why?

Question

Can anyone explain why there is type mismatch when If range... runs?
here is my code

Sub Button2_Click()
Dim i As Integer
Dim k As Integer
For i = 2 To 1000
For x = 3 To 999
If Range("k" & i & ":bn" & i).Value = Range("k" & x & ":bn" & x).Value And Cells(i, 5).Value <> Cells(x, 5).Value Then
Range("k" & i & ":bn" & i).Interior.ColorIndex = 7
Range("k" & x & ":bn" & x).Interior.ColorIndex = 7
End If
Next x
Next i

End Sub

I tried to use Cstr() but nothing changed

UP: I've tried to use one more loop and cells instead of range and only thing I get is application-defined or object-defined error for this:

Dim z As Integer
...
For z = 6 To 30
If Cells(i, z).Value = Cells(x, z).Value And Cells(i, 5).Value <> Cells(x, 5).Value Then

tnx in advance

You can't compare entire range like this: `Range("K2:BN2").Value=Range("K3:BN3").Value`. See this question on how to do it: http://stackoverflow.com/questions/19395633/how-to-compare-two-entire-rows-in-a-sheet/19396257#19396257 — Dmitry Pavliv, Mar 19 '14 at 12:28
you can compare only _one_ cell with _another_ cell. But you can't compare _entire ranges_ in that way. If you need compare entire ranges - see the link I gave you above — Dmitry Pavliv, Mar 19 '14 at 12:31
@simoco but when I use cells instead of range I get application-defined or object-defined error :( — Seya, Mar 19 '14 at 12:34
it seems that your cells contains error in worksheet (like `#N/A`, `#DIV/0!`, `#VALUE!` and so on. Check please if it's true for you? — Dmitry Pavliv, Mar 19 '14 at 12:37
@simoco I've made dumb mistake with variable name, sorry, code is ok. But working not as needed, such a pity) — Seya, Mar 19 '14 at 12:39
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/50030/discussion-between-simoco-and-seya) — Dmitry Pavliv, Mar 19 '14 at 12:39

score 2 · Accepted Answer · answered Mar 19 '14 at 13:22

The problem is that you are trying to compare two ranges of the same "shape" but more than one cell. Excel VBA does not allow this..something like:

Sub test1()
    Dim r1 As Range, r2 As Range
    Set r1 = Range("A1:A2")
    Set r2 = Range("B1:B2")
    If r1.Value = r2.Value Then
        MsgBox "same"
    End If
End Sub

will fail.............you need an element-by-element comparison like:

Sub test2()
    Dim r1 As Range, r2 As Range
    Set r1 = Range("A1:A2")
    Set r2 = Range("B1:B2")
    Dim i As Long, b As Boolean
    b = True
    For i = 1 To 2
        If r2(i).Value <> r1(i).Value Then
            b = False
        End If
    Next i
    If b Then
        MsgBox "same"
    End If
End Sub

Type mismatch error, why?

1 Answers1