comparing the rows in same worksheets

Question

I am a beginner.

I should write a program, that compares the all values in all rows in column A, B, C, D, E, with the values in columns G, H, I, J and K in the same row.

If they are all equal, then I need to highlight the row. I wrote the following code, but it does not execute:

Sub compare()

Dim C As Range
Dim D As Range
Dim lastRow As Integer
Dim rng As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer

lastRow = ThisWorkbook.Worksheets("ComparingResult").UsedRange.Rows.Count
'lastColumn = ThisWorkbook.Worksheets("ComparingResult").UsedRange.Columns.Count

Set rng = Range("K:K")
rng.Value = Format(rng.Value, "dd.mm.yyyy")

For i = 2 To lastRow
    For j = 1 To 5
        For k = 7 To 11
            For Each C In ThisWorkbook.Worksheets("ComparingResult").Cells(i, j)
                For Each D In ThisWorkbook.Worksheets("ComparingResult").Cells(i, k)
                    If C = D Then
                        C.Interior.Color = RGB(102, 255, 255)
                        D.Interior.Color = RGB(102, 255, 255)
                    Else
                        C.Interior.Color = vbWhite
                        D.Interior.Color = vbWhite
                    End If
                Next k
            Next j
        Next i
    Next
Next
End Sub

Can somebody help me figure out what is wrong with it?

So you need to ask an actual question. Does your code not work, does it do something but not what you intended? Be as specific as possible. — SJR, Sep 20 '18 at 10:12
Could you highlight what the problem is that you are seeing? Cheers! — d219, Sep 20 '18 at 10:12
btw, just because Integer sounds kuh-ool, there is no reason to ever use it. A Long is actually a Long Integer and should be used in all cases of declaring a var that is to accept a whole number (without fraction or decimal component). — , Sep 20 '18 at 10:18
@Jeeped This is not completely true. See [this question and answer](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) — Rik Sportel, Sep 20 '18 at 14:08
Start by looking into all your nested loops. Also "doesn't execute" might be true, it will be useful to tell us where your error occurs and what the error is. — Rik Sportel, Sep 20 '18 at 14:10
I change my code but even now it does not work I will appreciate you if you can help me — Liza, Sep 20 '18 at 14:20
actually no comparing happen in my program . and I changed the line For Each C In ThisWorkbook.Worksheets("ComparingResult").Cells(i, j).Value the error is that object required — Liza, Sep 20 '18 at 14:24
`For Each C In ThisWorkbook.Worksheets("ComparingResult").Cells(i, j)` - `Cells(i,j)` is looking at a single cell. You can't loop through a single cell. — Darren Bartrup-Cook, Sep 20 '18 at 15:06
Maybe change those two lines to `Set C = ThisWorkbook.Worksheets("ComparingResult").Cells(i, j)` and likewise for the other one. — Darren Bartrup-Cook, Sep 20 '18 at 15:07
Also your `Next` lines don't match up - `Next k` is paired up with `For Each D` - so `Invalid Next control variable reference`. — Darren Bartrup-Cook, Sep 20 '18 at 15:13

score 0 · Accepted Answer · answered Sep 20 '18 at 15:23

Think this is what you're after.
It will match A2 with G2:K2, B2 with G2:K2... up to E2 with G2:K2. Then A3 matches with G3:K3, etc...

You can probably do this with conditional formatting.

Sub compare()

    Dim C As Range
    Dim D As Range
    Dim lastRow As Integer
    Dim rng As Range
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer

    lastRow = ThisWorkbook.Worksheets("ComparingResult").UsedRange.Rows.Count
    'lastColumn = ThisWorkbook.Worksheets("ComparingResult").UsedRange.Columns.Count

    Set rng = Range("K:K")

    rng.NumberFormat = "dd.mm.yyyy"

    For i = 2 To lastRow
        For j = 1 To 5
            For k = 7 To 11
                Set C = ThisWorkbook.Worksheets("ComparingResult").Cells(i, j)
                Debug.Print C.Address

                Set D = ThisWorkbook.Worksheets("ComparingResult").Cells(i, k)
                Debug.Print D.Address

                If C = D Then
                    C.Interior.Color = RGB(102, 255, 255)
                    D.Interior.Color = RGB(102, 255, 255)
                Else
                    C.Interior.Color = vbWhite
                    D.Interior.Color = vbWhite
                End If
            Next k
        Next j
    Next i

End Sub

comparing the rows in same worksheets

1 Answers1