Highlight Rows Based on the Sum of Multiple Cells

[Captain_Conman]

Hello!

I currently have the following macro that highlights "offsets" in my spreadsheet.

Sub Offsets()


Dim Cell As Variant


For Each Cell In Range("B2:B2500")


Dim rng As Range
Set rng = Range(Cell, Cell.Offset(1, 0))


If Application.WorksheetFunction.CountIf(Range("B2:B2500"), Cell) > 1 Then
    If Cell.Offset(0, 1).Value = Cell.Offset(1, 1).Value * -1 Then
    rng.EntireRow.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    End If
    End If
Next


End Sub

For example, the following red transactions would be an offset.

Store #	Purchase Order	Amount	Class
Store 1513	A015130005080 <tbody> </tbody>	12.43	Debit Memo
Store 1513	A014460005574 <tbody> </tbody>	7.14	Debit Memo
Store 1513	A014460005574	(7.14)	Credit Memo
Store 1583	A015950005582 <tbody> </tbody>	(78.67)	Credit Memo
Store 1583	A015970001912 <tbody> </tbody>	19.44	Debit Memo

<tbody>
</tbody>

However, there are sometimes three or four lines that can offset each other as seen in red below.

Store #	Purchase Order	Amount	Class
Store 1513	A015130005080 <tbody> </tbody>	12.43	Debit Memo
Store 1513	A014460005574 <tbody> </tbody>	7.14	Debit Memo
Store 1541	A015130004085 <tbody> </tbody>	(25.42)	Credit Memo
Store 1541	A015130004085 <tbody> </tbody>	(25.42)	Credit Memo
Store 1541	A015130004085 <tbody> </tbody>	42.37	Debit Memo
Store 1541	A015130004085 <tbody> </tbody>	50.82	Debit Memo
Store 1541	A014340077651 <tbody> </tbody>	96.75	Debit Memo
Store 1583	A015950005582 <tbody> </tbody>	(78.67)	Credit Memo
Store 1583	A015970001912 <tbody> </tbody>	19.44	Debit Memo

<tbody>
</tbody>

I am trying to write a macro that will look through matching POs and highlight multiple rows that sum to zero, as seen in the example above. I can't quite seem to figure this one out.

I hope this is clear, any advice is greatly appreciated.

 

[Captain_Conman]

The functionality of the second code you posted (Post #7 ) is exactly what I need. However, being that it cannot address more than 15 unique identifiers, is there a way to exempt anything greater than 15 from the code? For example, if the unique identifier in column A > 15 cut and paste these lines into sheet two, and continue running the macro on sheet 1?

 

[Captain_Conman]

Mick,

I actually think I just found a way to reduce the amount of data that will be pulled during the export. This should reduce the amount of unique identifiers in column A and allow the macro to work properly! Again, I cannot thank you enough for all the help you have provided. I could not have done this without you and really appreciate your work. THANK YOU THANK YOU THANK YOU!!!!!

 

[MickG]

You're very welcome