how to avoid infinite loops ine excel vba ?

**royUK** · 10-25-2011, 02:22 AM

Something like this

Option Explicit

Sub IbanNo()
    Dim LR As Long
    Dim iX As Long
    Dim rRng As Range
    LR = ActiveSheet.UsedRange.Rows.Count

    For iX = 4 To LR

        If Left(Cells(iX, 16), 4) = "PT50" Then
            If rRng Is Nothing Then
                Set rRng = Cells(iX, 16)
            Else: Set rRng = Union(rRng, Cells(iX, 16))
            End If
        End If
    Next iX
    With rRng
        .Interior.ColorIndex = 3
    End With
End Sub

**meen2525** · 10-25-2011, 02:32 AM

Hii RoyUk,
Still a problem but Thanks a lot for replying

. The infinite loop has stopped now :D yippee..
but the problem now is its higlighting the cells which STARTS with PT50. I want it other way round. Highlight the cells NOT STARTIN with PT50. wat to dooo ? plllleaaase help.Thaaaaaanks in advance.

**meen2525** · 10-25-2011, 02:41 AM

Hiii,
I found a way to overcome it.But it colours all other blank cells too

. can we JUST color those cells NOT STARTING with PT50 ? any expert advice on this ?? Thaaaaanks in advance

Please find the code below i modified from yours.

Code:

Option Explicit

Sub IbanNo()
    Dim LR As Long
    Dim iX As Long
    Dim rRng As Range
    LR = ActiveSheet.UsedRange.Rows.Count

    For iX = 4 To LR

        If Left(Cells(iX, 16), 4) <> "PT50" Then
            If rRng Is Nothing Then
                Set rRng = Cells(iX, 16)
            Else: Set rRng = Union(rRng, Cells(iX, 16))
            End If
        End If
    Next iX
    With rRng
        .Interior.ColorIndex = 3
    End With
End Sub