Captain_Conman
Board Regular
- Joined
- Jun 14, 2018
- Messages
- 54
Hello!
I currently have the following macro that highlights "offsets" in my spreadsheet.
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.
I currently have the following macro that highlights "offsets" in my spreadsheet.
Code:
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.
Last edited: