Getting Run Time Error 9: Sub script out of Range.

[navbains]

I am trying to compare two columns from two different sheets. I cannot use conditional formatting because then it does allow you to use VBA macros for that cells. Basically, I have my second sheet highlighted in blue and if a match is found in second it will reset the color to none. Below is my code.

Sub Nav()


Dim ws1 As Worksheet
Dim ws2 As Worksheet


Set ws1 = Worksheets("Controller")
Set ws2 = Worksheets("Order Conversion")
For Each i In ws1.Range("B2:B3000")
For Each C In ws2.Range("A2:A3000")
If i.Cells.Value = C.Cells.Value Then
C.Cells.Interior.ColorIndex = xlNone
End If
Next i
Next C
End Sub

In the both the sheets I have data till row #500 but I want to get this done by 3000 rows because data is updated everyday in sheet one.

Any help please.
Thanks.

[JOHN H. DAVIS]

Please use code tags when posting.

Maybe?

Sub navbains()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Long

Set ws1 = Worksheets("Controller")
Set ws2 = Worksheets("Order Conversion")
For i = 2 To 3000
    If ws1.Cells(i, "B") = ws2.Cells(i, "A") Then
        ws2.Cells(i, "A").Interior.ColorIndex = xlNone
    End If
Next i
End Sub

[navbains]

Please use code tags when posting.

Maybe?

Sub navbains()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Long

Set ws1 = Worksheets("Controller")
Set ws2 = Worksheets("Order Conversion")
For i = 2 To 3000
    If ws1.Cells(i, "B") = ws2.Cells(i, "A") Then
        ws2.Cells(i, "A").Interior.ColorIndex = xlNone
    End If
Next i
End Sub

Hi , it is still giving subscript out of range error.

thanks

[JOHN H. DAVIS]

Hi , it is still giving subscript out of range error.

thanks

Double check the spelling of your sheets name to ensure they match the actual names in your workbooks. Also are both worksheets in the same workbook?

[navbains]

Double check the spelling of your sheets name to ensure they match the actual names in your workbooks. Also are both worksheets in the same workbook?

Yeah, I checked. They are same.

Thanks

[skywriter]

Yeah, I checked. They are same.

Thanks

No they are not the same. John was right, you have a space after order conversion. Also the C loop should be the first in the next statement. See my change.

So either take the space out after the sheet name or go into the code and add a space after the sheet name and before the ". Then run this code, or John's whichever is your choice. But if you run his you have to do the same thing either fix the sheet name or add the space in the code.

Dim lr1 As Long, lr2 As Long

Set ws1 = Worksheets("Controller")
Set ws2 = Worksheets("Order Conversion")
lr1 = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
lr2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
For Each i In ws1.Range("B2:B" & lr1)
For Each C In ws2.Range("A2:A" & lr2)
If i.Value = C.Value Then
C.Interior.ColorIndex = xlNone
End If
Next C
Next i
End Sub

[skywriter]

See if this works for you. If not maybe you can post the sheet.

Sub Nav()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Range, C As Range
Dim lr1 As Long, lr2 As Long

Set ws1 = Worksheets("Controller")
Set ws2 = Worksheets("Order Conversion")
lr1 = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
lr2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
For Each i In ws1.Range("B2:B" & lr1)
For Each C In ws2.Range("A2:A" & lr2)
If i.Value = C.Value Then
C.Interior.ColorIndex = xlNone
End If
Next i
Next C
End Sub

[navbains]

Hi Skywriter,
I tried your code but it give me " Invalid Next Control Variable reference" and highlights at Next i.

Sorry, I am pretty new to this programming world.

Could you please help.

[skywriter]

Hi Skywriter,
I tried your code but it give me " Invalid Next Control Variable reference" and highlights at Next i.

Sorry, I am pretty new to this programming world.

Could you please help.

Read above the code in Post #3.

[navbains]

Read above the code in Post #3.

Hi I have attached the file.

Thanks
Nav