Macro which highlights matching debits and credits?

[Coyotex3]

Hello, I'm trying to get help with a macro that highlights matching debits and credits. I would like for the macro to go from this

Book1
	A	B	C
1	Property	Debit	Credit
2	Central	500	0
3	Central	600	0
4	Central	700	0
5	Central	600	0
6	Central	500	0
7	Central	500	0
8	Central	600	0
9
10	Central	0	600
11	Central	0	700
12	Central	0	600
13	Central	0	500
14	Central	0	500
15	Central	0	700
16	Central	0	800
Accounts

To this:

Book1
	A	B	C	D
1	Property	Debit	Credit
2	Central	500	0
3	Central	600	0
4	Central	700	0
5	Central	600	0
6	Central	500	0
7	Central	500	0
8	Central	600	0
9
10	Central	0	600
11	Central	0	700
12	Central	0	600
13	Central	0	500
14	Central	0	500
15	Central	0	700
16	Central	0	800
17
Accounts

 

[Marc L]

Hello, according to your attachment a VBA demonstration for starters :​

Sub Demo1()
        Dim L&, V, R&, W
    With Sheets("Accounts")
            L = .[A1].CurrentRegion.Rows.Count
            V = .Cells(L + 2, 1).CurrentRegion.Columns(3).Value2
        For R = 2 To L
                W = Application.Match(.Cells(R, 2).Value2, V, 0)
            If IsNumeric(W) Then
                Union(.Cells(R, 2), .Cells(L + W + 1, 3)).Interior.Color = vbYellow
                V(W, 1) = Empty
            End If
        Next
    End With
End Sub

 

[Coyotex3]

This is great mate thank you!!

 

[Coyotex3]

Hello, according to your attachment a VBA demonstration for starters :​

Sub Demo1()
        Dim L&, V, R&, W
    With Sheets("Accounts")
            L = .[A1].CurrentRegion.Rows.Count
            V = .Cells(L + 2, 1).CurrentRegion.Columns(3).Value2
        For R = 2 To L
                W = Application.Match(.Cells(R, 2).Value2, V, 0)
            If IsNumeric(W) Then
                Union(.Cells(R, 2), .Cells(L + W + 1, 3)).Interior.Color = vbYellow
                V(W, 1) = Empty
            End If
        Next
    End With
End Sub

Thank you this is perfect. I ran into a slightly different form of data, is there a way of making it work on this:

Book1
	A	B	C
1	Property	Debit	Credit
2	Central	500	0
3	Central	600	0
4	Central	700	0
5	Central	600	0
6	Central	500	0
7	Central	500	0
8	Central	600	0
9	Central	0	0
10	Central	0	600
11	Central	0	700
12	Central	0	600
13	Central	0	500
14	Central	0	500
15	Central	0	700
16	Central	0	800
17	Central	0	1000
Accounts

To this:

Book1
	A	B	C
1	Property	Debit	Credit
2	Central	500	0
3	Central	600	0
4	Central	700	0
5	Central	600	0
6	Central	500	0
7	Central	500	0
8	Central	600	0
9	Central	0	0
10	Central	0	600
11	Central	0	700
12	Central	0	600
13	Central	0	500
14	Central	0	500
15	Central	0	700
16	Central	0	800
17	Central	0	1000
Accounts

 

[Marc L]

My VBA demonstration revamped :​

Sub Demo1r()
        Dim V, W, R&, X
    With [Accounts!A1].CurrentRegion.Columns
           .Item("B:C").Interior.Pattern = xlNone
            V = .Item(2).Value2
            W = .Item(3).Value2
        For R = 2 To .Rows.Count
            If V(R, 1) Then
                X = Application.Match(V(R, 1), W, 0)
                If IsNumeric(X) Then Union(.Cells(R, 2), .Cells(X, 3)).Interior.Color = vbYellow: W(X, 1) = Empty
            End If
        Next
    End With
End Sub

 

[Coyotex3]

My VBA demonstration revamped :​

Sub Demo1r()
        Dim V, W, R&, X
    With [Accounts!A1].CurrentRegion.Columns
           .Item("B:C").Interior.Pattern = xlNone
            V = .Item(2).Value2
            W = .Item(3).Value2
        For R = 2 To .Rows.Count
            If V(R, 1) Then
                X = Application.Match(V(R, 1), W, 0)
                If IsNumeric(X) Then Union(.Cells(R, 2), .Cells(X, 3)).Interior.Color = vbYellow: W(X, 1) = Empty
            End If
        Next
    End With
End Sub

Thank you mate! This was great as well, and worked as intended!