VBA nested looping with do until loop

Question

I need a looping structure that checks a range of cells, then if the cell and a cell that is in the range equal each other then the font should turn red. My problem is that my do until loop won't get entered. This is what I have right now. `

Dim finalrow As Long
finalrow = Worksheets("Redundancy").Cells(Worksheets("Redundancy").Rows.Count, "D").End(xlUp).Row
Dim z As Long
Dim w As Long
Dim r As Long
w = 2
r = 0
For z = 2 To finalrow
     If Range("L" & z) = Range("L" & z + 1) & Range("J" & z) <> Range("J" & z + 1) Then
        Do Until Range("L" & z) = Range("L" & z + 1) & Range("J" & z) <> Range("J" & z + 1)
                If Cells(w, 4) = Cells(z + 1, 4 + r) Then
                    Cells(w, 1).Font.ColorIndex = 3
                    Cells(z + 1, 1).Font.ColorIndex = 3
                    If r = 4 Then
                        w = w + 1
                    End If
                End If
            r = r + 1
        Loop
    End If
 Next z

` I changed it to this, but it exits the loop all together right when it is about to enter the do while loop.

`

For z = 2 To finalrow
        Do While (Range("L" & z) = Range("L" & z + 1) And Range("J" & z) <> Range("J" & z + 1))
                If Cells(w, 4) = Cells(z + 1, 4 + r) Then
                    Cells(w, 1).Font.ColorIndex = 3
                    Cells(z + 1, 1).Font.ColorIndex = 3
                    If r = 4 Then
                        w = w + 1
                    End If
                End If
            r = r + 1
        Loop
 Next z

`

I don't think you can ever enter your loop. That condition(s) and the If above it seem identical. — user4039065
– user4039065, Commented Nov 10, 2017 at 22:14
Right. If something then do until something. You will never go into the Do loop. Perhaps you meant Do WHILE. — user4039065
– user4039065, Commented Nov 10, 2017 at 22:18
Do Until Range("L" & z) <> Range("L" & z + 1) OR Range("J" & z) = Range("J" & z + 1) — user4039065
– user4039065, Commented Nov 10, 2017 at 22:34

gr8tech · Accepted Answer · 2017-11-10 22:58:51Z

1

If you do this;

Range("L" & z) = Range("L" & z + 1) and Range("J" & z) <> Range("J" & z + 1)

you are comparing Range objects. What you instead want to do is to compare the values in those range objects. So use this instead;

Range("L" & z).value = Range("L" & z + 1).value and Range("J" & z).value <> Range("J" & z + 1).value

However when you use the cells(row,column) you don't have this problem.

I am curious though, was it not possible to use conditional formatting instead?

answered Nov 10, 2017 at 22:58

gr8tech

1721 silver badge7 bronze badges

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

1 Comment

Vegard Over a year ago

Value is the default member of Range, so in this instance there's actually no need to specify .Value (though I prefer it for readability).

Dan Grabski · Accepted Answer · 2017-11-10 22:29:11Z

0

Use the 'and' operator instead of '&'.

answered Nov 10, 2017 at 22:29

Dan Grabski

111 bronze badge

1 Comment

Maldred Over a year ago

This still doesn't fix his problem as his logic isn't correct

Collectives™ on Stack Overflow

VBA nested looping with do until loop

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related