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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store #[/TD]
[TD]Purchase Order[/TD]
[TD]Amount[/TD]
[TD]Class[/TD]
[/TR]
[TR]
[TD]Store 1513[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015130005080[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12.43[/TD]
[TD]Debit Memo[/TD]
[/TR]
[TR]
[TD]Store 1513[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A014460005574[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]7.14[/TD]
[TD]Debit Memo[/TD]
[/TR]
[TR]
[TD]Store 1513[/TD]
[TD]A014460005574[/TD]
[TD](7.14)[/TD]
[TD]Credit Memo[/TD]
[/TR]
[TR]
[TD]Store 1583[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015950005582[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD](78.67)[/TD]
[TD]Credit Memo[/TD]
[/TR]
[TR]
[TD]Store 1583[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015970001912[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19.44[/TD]
[TD]Debit Memo[/TD]
[/TR]
</tbody>[/TABLE]



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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store #[/TD]
[TD]Purchase Order[/TD]
[TD]Amount[/TD]
[TD]Class[/TD]
[/TR]
[TR]
[TD]Store 1513[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015130005080[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12.43[/TD]
[TD]Debit Memo[/TD]
[/TR]
[TR]
[TD]Store 1513[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A014460005574[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]7.14[/TD]
[TD]Debit Memo[/TD]
[/TR]
[TR]
[TD]Store 1541[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015130004085[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD](25.42)[/TD]
[TD]Credit Memo[/TD]
[/TR]
[TR]
[TD]Store 1541[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015130004085[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD](25.42)[/TD]
[TD]Credit Memo[/TD]
[/TR]
[TR]
[TD]Store 1541[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015130004085[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]42.37[/TD]
[TD]Debit Memo[/TD]
[/TR]
[TR]
[TD]Store 1541[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015130004085[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50.82[/TD]
[TD]Debit Memo[/TD]
[/TR]
[TR]
[TD]Store 1541[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A014340077651[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]96.75[/TD]
[TD]Debit Memo[/TD]
[/TR]
[TR]
[TD]Store 1583[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015950005582[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD](78.67)[/TD]
[TD]Credit Memo[/TD]
[/TR]
[TR]
[TD]Store 1583[/TD]
[TD][TABLE="width: 260"]
<tbody>[TR]
[TD="width: 260"]A015970001912[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19.44[/TD]
[TD]Debit Memo[/TD]
[/TR]
</tbody>[/TABLE]

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