Compare the numbers in 2 columns and reconcile

[abhay_547]

Hi, I am trying to compare 2 columns and find the match of numbers e.g. column A has 108.33 value and column B has the split of the same value .i.e. 33.33, 50 and 25 so the macro should loop through column A and B and find such matches and color code them into one unique color in both column A and B.


I have come across the below macro through my search but this is to find the exact matches. can someone help.

Sub Compare_Ranges()
'On Error Resume Next
Dim rng1 As Range
Dim rng2 As Range
Dim rCell As Range
Dim result As Byte

Set rng1 = ThisWorkbook.Sheets("Sheet1").Columns("A:A")
Set rng2 = ThisWorkbook.Sheets("Sheet1").Columns("B:B")
For Each rCell In rng1
rCell.Interior.ColorIndex = xlNone
rCell.Validation.Delete
result = WorksheetFunction.CountIf(rng2, rCell)
If result = 0 Then
rCell.Interior.ColorIndex = xlNone
ElseIf result = 1 Then
rCell.Interior.Color = vbGreen
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & "time occured in " & rng2.Address & "."
End With

ElseIf result = 2 Then
rCell.Interior.Color = vbYellow
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & " time occured."
End With

ElseIf result = 3 Then
rCell.Interior.Color = vbBlue
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & " time occured."
End With
ElseIf result = 4 Then
rCell.Interior.Color = vblavender
With rCell.Validation
.Add xlValidateInputOnly
.InputMessage = "The value is " & result & " time occured."
End With

End If
Next
End Sub