Sub finddupes()
dim found as Boolean
For Each mycell In Worksheets("Ark1").Range("B2:L34")
myvalue = Abs(mycell.Value)
If myvalue <> 0 Then
found = False
For Each chkcell In Worksheets("Ark1").Range("B2:L34")
If mycell <> chkcell Then
If myvalue = Abs(chkcell.Value) Then
chkcell.Interior.ColorIndex = 4 ' Green
mycell.Interior.ColorIndex = 4
found = True
End If
End If
Next
If found = False Then mycell.Interior.ColorIndex = 3 'Red
End If
Next
End Sub
A few words of thanks would be greatly appreciated. Add comment
5687 users have said thank you to us this month
DON'T MISS
I have added comments to explain what is going on and made important values variables so they only need to be set once. This makes it easier to modify for changes.
and -213.154,33333 the cells both turn red, where it should turn yellow.
It would be prefered though if 2 cells are equal before the decimals, that they turn green, but if not they should atleast go yellow, so I can check them afterwards.
Sorry for the inconvinience!
1. diff is now a Double variable type.
2.The comparison of the cell location was being done incorrectly.
If you want to ignore the decimal places then using these in place of the originals will do that for you:
and
This works perfectly now, thank you!