CODEWITHSUNDEEP
excelvba
Antoine Dargouge
Antoine Dargouge

Reputation: 41

VBA While loop does not iterate

I have a project and on it I must had to check if on the same line, "I" is empty "k" must be empty too. And if not I paint backgroud on red.

I do that code. Not crash but no effect too …

Sub JK()

Dim count As Integer
Dim emptyJ As Boolean
Dim emptyK As Boolean

    count = 1
    
While count = 999

    emptyJ = isEmpty(Cells(count, J).Value)
    emptyK = isEmpty(Cells(count, K).Value)

    If emptyJ = True Then
        If emptyK = False Then
            Range(Cells(J, count), Cells(K, count)).Select
            With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
            End With
        Else
        End If
    Else
    End If
    
    count = count + 1
    
 Wend

End Sub

Upvotes: 0

Views: 127

Answers (2)

Antoine Dargouge
Antoine Dargouge

Reputation: 41

Tanks you for your propositions. They works perfectly now. My mistakes come to the "K" cells who are lists with 2 propositions -" " and - "a texte". In dat case we can't sayd "K isEmpty". But with dat code

Sub JK()

    Dim count As Long
    
    ' Change the sheet name
    With ThisWorkbook.Sheets("Sheet 1")
        count = 1
        While count <= 999
            With .Cells(count, "J")
                If .Value = "" Then
                    If Not (.Offset(0, 1).Value) = "" Then
                        With .Resize(1, 2).Interior
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .Color = 255
                        End With
                    End If
                End If
            End With
            
            count = count + 1
        Wend
    End With
End Sub

that works

Or that one:

Sub JK()


For i = 1 To ActiveSheet.UsedRange.Rows.count
    
        If (Cells(i, 10).Value) = "" And Not (Cells(i, 11).Value) = "" Then
            Range(Cells(i, 10), Cells(i, 11)).Interior.Color = 255
        Else
        End If
    Next i
End Sub

Upvotes: 0

Holger
Holger

Reputation: 330

As stated in the comments above, I would solve the problem via conditional formatting. But if you would like to do this with VBA, the following code can help

Sub JK()
    For i = 1 To ActiveSheet.UsedRange.Rows.Count
    
        If IsEmpty(Cells(i, 10)) And Not IsEmpty(Cells(i, 11)) Then
            Range(Cells(i, 10), Cells(i, 11)).Interior.Color = 255
        Else
            Range(Cells(i, 10), Cells(i, 11)).Interior.Pattern = xlNone
        End If
    Next i
End Sub

Upvotes: 1

Related Questions