I have a column with transaction values. I want to create a FOR loop which will find the opposite number of a value (like finding -10 for 10) and highlight both the cells.
I have written a double FOR loop which takes a lot of time because there are more 2000 lines in the database.
Is there a faster way to do it?
TL;DR I want to find two equal and opposite values in a given range and highlight both of them.
Try this code
Sub HL_Opposite()
Set dict = CreateObject("Scripting.Dictionary")
t = Timer
Application.ScreenUpdating = False
With ActiveSheet
Set Rng = Intersect(.Columns("A"), .UsedRange)
Rng.Interior.ColorIndex = xlColorIndexNone
a = Rng ' get all the values from range to array (to speed up)
For Each v In a
If Not dict.Exists(v) Then dict.Add v, False
If dict.Exists(-v) Then
dict(v) = True ' mark the opposit entries as True
dict(-v) = True ' mark the opposit entries as True
End If
Next
For r = LBound(a) To UBound(a)
If dict(a(r, 1)) Or dict(-a(r, 1)) Then ' check if tha value marked as opposite
Rng(1).Offset(r - 1).Interior.Color = vbRed
End If
Next
End With
Application.ScreenUpdating = True
Debug.Print "Done in " & Timer - t & " sec."
End Sub
