Loop automatically kicks out if If statement is true VBA?

Question

I am working on an Excel macro that should find any cells with duplicate IDs and highlight them in red. For some reason, when it finds a duplicate it will exit out of the loop.  It cycles through fine if there are not any duplicates.  My code is below.

Sub FindNew()
'
' FindNew Macro
'
Dim numRowsThis As Integer
Dim numRowsData As Integer
Dim dataCell As String
Dim thisCell As String

numRowsThis = Sheet5.Cells(2, 2).Value
numRowsData = Sheet5.Cells(1, 2).Value


Dim i As Integer
Dim j As Integer

For i = 1 To numRowsData
dataCell = "A" & LTrim(Str(i))

For j = 4 To numRowsThis
thisCell = "B" & LTrim(Str(j))

If Sheet3.Range(dataCell).Value = Sheet5.Range(thisCell).Value Then
Range(dataCell).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range(thisCell).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If

Next
Next
'
End Sub

puaka · Accepted Answer

Glad you are experimenting with your macros. Some tips

Range(dataCell).Select could mess up your macro as it makes it the new activecell.

Create 2 named ranges by using Set keyword

dim rangeA as range
set rangeA = sheet1.range("a1:a20")
or set rangeA = sheet1.range("a1").resize(20,1)
' I prefer these to cells(n,n) convention which is hard to read

Next run a loop within a loop.

dim cella, cellb as range
For each cella in rangeA
For each cellb in rangeB
  if cella = cellb then
    with cella 'there is no need to select the cell to do any changes
      .color = vbred
    end with
Next
Next

Trending News

Loop automatically kicks out if If statement is true VBA?

1 Answer