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.
<tbody>
</tbody>
However, there are sometimes three or four lines that can offset each other as seen in red below.
<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.
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.
Store # | Purchase Order | Amount | Class | |
Store 1513 |
<tbody> </tbody> | 12.43 | Debit Memo | |
Store 1513 |
<tbody> </tbody> | 7.14 | Debit Memo | |
Store 1513 | A014460005574 | (7.14) | Credit Memo | |
Store 1583 |
<tbody> </tbody> | (78.67) | Credit Memo | |
Store 1583 |
<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 |
<tbody> </tbody> | 12.43 | Debit Memo | |
Store 1513 |
<tbody> </tbody> | 7.14 | Debit Memo | |
Store 1541 |
<tbody> </tbody> | (25.42) | Credit Memo | |
Store 1541 |
<tbody> </tbody> | (25.42) | Credit Memo | |
Store 1541 |
<tbody> </tbody> | 42.37 | Debit Memo | |
Store 1541 |
<tbody> </tbody> | 50.82 | Debit Memo | |
Store 1541 |
<tbody> </tbody> | 96.75 | Debit Memo | |
Store 1583 |
<tbody> </tbody> | (78.67) | Credit Memo | |
Store 1583 |
<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.
Last edited: