VBA: Error 13, type mismatch

Question

I want to let Excel automatically change the color of a cell if that cell's value is either larger or smaller by 10% than the cell that at same row but three columns to the left.

Here is the code:

Sub testing1()
    Dim x As Integer
    Dim y As Integer

    For x = 35 To 5 Step -3
        For y = 11 To 75 Step 1
            If Cells(y, x).Value < 0.9 * Cells(y, x - 3).Value Or _
               Cells(y, x).Value > 1.1 * Cells(y, x - 3) Then
                Cells(y, x).Interior.ColorIndex = 22
            End If
        Next y
    Next x
End Sub

Before I defined y, I used a single row, like 11. It is like this: cells(11,x).value; the code ran without problems.

However, when I change from numbers to variable y, I get an error: Error 13, type mismatch.

What is the source of this error?

You should format your code as such. There is an orange ? that you can click when editing to see how. — crashmstr
– crashmstr, Commented Aug 24, 2015 at 19:46
Why wouldn't you just use Conditional Formatting and not worry about coding? — jradich1234
– jradich1234, Commented Aug 24, 2015 at 19:56

WltrRpo · Accepted Answer · 2015-08-24 20:49:32Z

1

put one more condition layer. Actually if you have a cell with a in it and you multiply it by 5 , you usually get a type mismatch error.

If IsNumeric(Cells(y, x)) Then
    If Cells(y, x).Value < 0.9 * Cells(y, x - 3).Value Or Cells(y, x).Value > 1.1 * Cells(y, x - 3) Then

        Cells(y, x).Interior.ColorIndex = 22

    End If
End If

edited Aug 24, 2015 at 20:49

WltrRpo

2632 silver badges13 bronze badges

answered Aug 24, 2015 at 20:13

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cljsibyl123 Over a year ago

I found my data contains "#Div/0!", that caused the issue. I did the code you suggested, it works! Thank you very much.

Collectives™ on Stack Overflow

VBA: Error 13, type mismatch

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related