Why am I getting runtime error 13 type mismatch?

hunter9002

New Member
Joined
Jun 1, 2012
Messages
8
I am trying to use vba to look at each number in Sheet3 Column A and see if it exists on either Sheet1 Column G or Sheet2 Column G. If it exists on the former, I want to color the row green on both sheets. If it exists on the latter, I want to color the row yellow on both sheets.

I am not sure why I am getting a type mismatch. Can anyone help me fix this? Thank you!

Private Sub CommandButton1_Click()
Dim num As Integer
Dim counter As Integer

For counter = 2 To 1218

If Sheet3.Cells(counter, 1).Value = Sheet1.Range("g2:g9063") Then
num = Sheet1.Range("g2:g9063")
Sheet3.Rows(counter) = vbGreen And Sheet1.Rows(num) = vbGreen

ElseIf Sheet3.Cells(counter, 1).Value = Sheet2.Range("g2:g1643") Then
num = Sheet1.Range("g2:g1643")
Sheet3.Rows(counter) = vbYellow And Sheet2.Rows(num) = vbYellow
End If

Next counter
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try like this

Code:
If IsNumeric(Application.Match(Sheet3.Cells(counter, 1).Value, Sheet1.Range("g2:g9063"), 0)) Then
 
Upvote 0
Still getting the same error

Unless, did you want me to add this if statement to the code, or replace my original if statement with this one? I just replaced my if with yours.
 
Last edited:
Upvote 0
Well you'll need to amend those tests as I showed. You will get a mismatch if you try to compare the value of one cell to the value of a range. This test worked for me

Code:
Sub atest()
Dim counter As Long
counter = 5
If IsNumeric(Application.Match(Sheet3.Cells(counter, 1).Value, Sheet1.Range("g2:g9063"), 0)) Then
    MsgBox "Yes!"
End If
End Sub
 
Upvote 0
I am a bit out of practice with VBA, please excuse my errors. I appreciate the help. The test you posted worked for me, but I'm still getting the error. Here is my current code:

Private Sub CommandButton1_Click()
Dim counter As Integer
For counter = 2 To 1218

If IsNumeric(Application.Match(Sheet3.Cells(counter, 1).Value, Sheet1.Range("g2:g9063"), 0)) Then
Rows(Sheet1.Range("g2:g9063")) = vbGreen And Sheet3.Rows(counter) = vbGreen

ElseIf IsNumeric(Application.Match(Sheet3.Cells(counter, 1).Value, Sheet1.Range("g2:g9063"), 0)) Then
Rows(Sheet2.Range("g2:g1643")) = vbYellow And Sheet3.Rows(counter) = vbYellow
End If
Next counter
End Sub
 
Upvote 0
I think it should be like this

Code:
Private Sub CommandButton1_Click()
Dim counter As Long
Dim X As Variant
For counter = 2 To 1218
    X = Application.Match(Sheet3.Cells(counter, 1).Value, Sheet1.Range("g2:g9063"), 0)
    If IsNumeric(X) Then
        Sheet1.Rows(X).Interior.Color = vbGreen
        Sheet3.Rows(counter).Interior.Color = vbGreen
    End If
    X = Application.Match(Sheet3.Cells(counter, 1).Value, Sheet2.Range("g2:g9063"), 0)
    If IsNumeric(X) Then
        Sheet2.Rows(X).Interior.Color = vbGreen
        Sheet3.Rows(counter).Interior.Color = vbGreen
    End If
Next counter
End Sub
 
Upvote 0

Forum statistics

Threads
1,203,224
Messages
6,054,241
Members
444,711
Latest member
Stupid Idiot

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top