VBA While loop does not iterate

Question

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

For starters, you probably want While count <= 999. -- Debugging VBA Code — Andre
– Andre, Commented Jul 29, 2020 at 13:49
Is it important for you to solve this via VBA? Otherwise I would do something like this with plain Excel via conditional formatting — Holger
– Holger, Commented Jul 29, 2020 at 13:51
I forbide Not before count = 999 my bad .. but steel Don't working. I ask seriusly my self about isEmpty … — Antoine Dargouge
– Antoine Dargouge, Commented Jul 29, 2020 at 13:56

Holger · Accepted Answer · 2020-07-29 14:46:09Z

1

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

edited Jul 29, 2020 at 14:46

answered Jul 29, 2020 at 14:10

Holger

3301 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Antoine Dargouge Over a year ago

Ya i try dat before but unfortunately does'nt work.at all. Like Super Symmetry proposition dat color all line where "J" is empty and not "J" empty & "K" not. I will try something new. Stay here if anyone had proposition

Holger Over a year ago

The code snippet above marks the two cells of each row where J is empty and K is not. What exactly do you want to have? Could you show us an example?

Antoine Dargouge Over a year ago

Dat's good i just got an invisible list at J with empty and "atext" so he can't indentify this empty.. Thanks u 2 for all

shreyasm-dev · Accepted Answer · 2021-01-05 02:07:42Z

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

Collectives™ on Stack Overflow

VBA While loop does not iterate

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related