0

I am iterating trough a range and want to highlight the repeating ones.

I tried to write a sub that checks if any value in a range repeats itself, and if it does, I want to highlight it

Later on I want to check if that value repeats itself more than x times (but that is straight forward, just add a counter and increment it)

I tried using two different methods

Method 1

Sub Highlight()

Dim count, countMax As Integer
countMax = 100
Dim rng As range, cell As range
Set rng = range("G1:G100")

For Each cell In rng

    For count = 1 To countMax
        Selection.Offset(1, 0).Select

        If Not IsEmpty(cell) And cell.Value = Selection.Value Then

            cell.Interior.color = RGB(0, 255, 0)

        End If
        countMax = countMax - 1
    Next count
Next cell

End Sub

Method 2

Sub Highlight()

Dim rngG1 As range, cellG1 As range
Set rngG1 = range("G1:G100")

Dim rngG2 As range, cellG2 As range
Set rngG2 = range("G1:G100")

For Each cellG1 In rngG1

    For Each cellG2 In rngG2
        If cellG1 <> cellG2 And Not IsEmpty(cellG1) And cellG1.Value = cellG2.Value Then
            cellG2.Interior.color = RGB(0, 255, 0)
        End If
    Next cellG2
Next cellG1

End Sub

Both methods don't highlight anything, please help with some information or point me in a direction.

Thanks.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Cereal Beard
  • 1
  • 2
  • 2
    Might want to look here: https://stackoverflow.com/questions/56854086/how-to-highlight-duplicates-in-column-that-are-not-blanks/56854573#56854573 – Warcupine Jul 05 '19 at 12:10
  • 1
    Is there a reason you must use VBA? this could be done with conditional formatting – cybernetic.nomad Jul 05 '19 at 15:31
  • as I mentioned in the post "Later on I want to check if that value repeats itself more than x times" and only if it repeats itself more than x times, I want to highlight all the cells containing that values I tried finding a rule that does this but didn't find anything of that sort What I am trying to do now is adding all the values in a list and iterate trough a list – Cereal Beard Jul 08 '19 at 06:48

0 Answers0