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

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.

Please use code tags when posting.

Maybe?

Please Login or Register  to view this content.

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.

Originally Posted by JOHN H. DAVIS

Please use code tags when posting.

Maybe?

Please Login or Register  to view this content.

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

thanks

Originally Posted by skywriter

Read above the code in Post #3.

Hi I have attached the file.

Thanks
Nav

Originally Posted by navbains

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?

Originally Posted by JOHN H. DAVIS

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

Originally Posted by skywriter

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.

Please Login or Register  to view this content.

Wooww.. Thanks a lot guys. I was struggling with this from past one day. Both you have a great long weekend. I just learned a lesson here, the main step for programming world is attention to detail.

Happy Easter Holidays.