So, I'm learning VBA and am trying to get this code to run through column Q starting from row 3 of Sheet 1, and compare its results with column F starting from row 4 of Sheet 2. If true, highlight the offending cell with yellow (I'd rather highlight the entire row, if you could help with that, it'd be fantastic!).

Sub CompareValues()

    Set ws1 = Application.Workbooks("Book1.xlsx").Sheets("Sheet1")
    Set ws2 = Application.Workbooks("Book2.xls").Sheets("Sheet2") ' the xls is correct
    

    startRow = 3
    

    ws1Value1Col = "Q"
    ws1EndRow = ws1.UsedRange.Rows(ws1.UsedRange.Rows.Count).Row
    

    ws2Value1Col = "F"
    ws2EndRow = ws2.UsedRange.Rows(ws2.UsedRange.Rows.Count).Row
    

    For i = startRow To ws1EndRow
        If Not ws1.Cells(i, ws1Value1Col).Value = ws2.Cells(i   1, ws2Value1Col).Value Then
            wsl.Cells(i, ws1Value1Col).Value = vbYellow
        End If
    Next

End Sub

I'm getting error 424: Object required.

After googling for the past hour I am not any closer to solving this. I've tried multiple suggestions to no avail. Both workbooks and worksheets are open at runtime.

CodePudding user response：

I've built a solution to this that follows along the initial point made by BigBen to use Option Explicit at the top of the module you're building.

The reason for doing so is because VBA will force you to define the type for every variable that you intend to use, and the below solution will show you that. Otherwise, the implicitly understood type is Variant, which has its applications, but is also very inefficient from a memory use point of view.

Option Explicit

Sub CompareValues()
    
    Dim ws1 As Worksheet, ws2 As Worksheet, startRow As Integer
    Dim ws1Col As Integer, ws2Col As Integer, ws1EndRow As Integer, ws2EndRow As Integer
    Dim i As Integer
    Dim cell As Range

    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")

    startRow = 3

    ws1Col = 1
    ws1EndRow = ws1.UsedRange.Rows(ws1.UsedRange.Rows.Count).Row
    
    ws2Col = 2
    ws2EndRow = ws2.UsedRange.Rows(ws2.UsedRange.Rows.Count).Row
    
    For i = startRow To ws1EndRow
        If Not ws1.Cells(i, ws1Col).Value = ws2.Cells(i   1, ws2Col).Value Then
            Set cell = ws1.Cells(i, ws1Col)
            cell.Interior.Color = vbYellow
        End If
    Next

End Sub

Let's look through this:

1. I'm defining your two worksheet variables, then the columns, end rows and the start row as integers.
2. Then I use a cell variable as a range object, whose purpose is to solve the error that you're experiencing.
3. The code as you have it to define the used range for each column is good, and an alternative is to use "End(xlUp).Row", as a way to set a dynamic range of data.
4. For Loop and If-Then conditions that you have are standard issue and appropriate.
5. The new moment here is that I am going to use the Set keyword to assign the needed cell to its namesake variable in the code.
6. Afterward, you can use the Interior.Color method to highlight the value you need as yellow.

Note that I've used integers to designate the columns in this version of the code. Your way does not error out, but if you are going to use Cells, then I think you should stick to uniform integer types for referencing row and column; that is a general good practice for coding, as well.

Hope this helps!