Hi Ram_G
This Code identifies about 69% of the Offsets and appears to identify all that are in your Sample File. I'll need more examples to find the outliers.
I've not yet addressed the Results Sheet nor the Orange Colors (I'm terribly Color Blind...I assume Orange).
Option Explicit
Sub Find_Offsets()
Dim ws As Worksheet
Dim Rng As Range, FindRng As Range, cel As Range, cel1 As Range, oSet As Range
Dim LR As Long, i As Long, j As Long
Set ws = Sheets("Report (Raw Input)")
With ws
LR = .Range("H" & .Rows.Count).End(xlUp).Row - 1
Set Rng = .Range("H7:H" & LR)
For Each cel In Rng
Set FindRng = .Range(.Cells(cel.Row, "J"), .Cells(cel.Row, "P"))
' FindRng.Select
Set oSet = FindRng.Find(cel.Value, , xlFormulas, xlWhole, xlByRows, xlNext, False)
If Not oSet Is Nothing Then
oSet.Interior.Color = 5296274
.Cells(cel.Row, "Q").Interior.Color = 5296274
ElseIf CStr(Application.Sum(FindRng)) = cel.Value Then
For Each cel1 In FindRng
If Not cel1.Value = 0 Then
cel1.Interior.Color = 5296274
End If
Next cel1
.Cells(cel.Row, "Q").Interior.Color = 5296274
Else
End If
Next cel
End With
End Sub
Bookmarks