Need help looping macro

Question

I don't believe this is very difficult but I can't figure it out...

In column B, I have either "Original" or "Add" listed. Starting from B79 and moving upwards, the first time "Original" is displayed I want to draw a border from B#:N# on the bottom.

I don't know how to run proper loops within VBA so below is what I have so far which is missing quite a bit.

Sub Test()    
Range("B79").Select
If Range("B79") = "Original" Then
Selection.End(xlToRight).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
    .LineStyle = xlDot
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlThin
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Else: ActiveCell.Offset(-1, 0).Select
End If

End Sub

Here is my current attempt. I am just trying to get it to highlight the cells.

Sub Test()

Let x = 79
Do While x > 7
If ("B" & x) = "Original" > 0 Then
Selection.End(xlToRight).Select
Else: x = x - 1
End If
Loop

End Sub

Researching about "VBA loops" should tell you everything you ever dreamed to know about `For...Next`, `Do...Loop`, and every one of the 6 or 7 different (yet very similar) loop constructs in VBA. What have you tried, and how is it failing/not working? Can concatenate a number into a string to make a `Range` address? If so, then you can do `For i = 79 To 1 Step -1....Next`, and use the value of `i` to track the "current row". — Mathieu Guindon, Nov 12 '18 at 18:11
I wouldn't be posting on this site if I haven't already tried researching this on my own... I also didn't want to post my fruitless attempts which would cause extra confusion. I've been able to cause a loop but not end it. — Matt9080, Nov 12 '18 at 18:29
On the contrary, posting your fruitless attempts *shows us that you've tried something and how and why it's failing*, as opposed to looking like you're just asking for others to do the work for you - the more effort you've put in, the less your question looks like "gimmeh teh codez" and the more well-received it gets =) — Mathieu Guindon, Nov 12 '18 at 18:31
That said you'll want to [avoid coding against Selection](https://stackoverflow.com/q/10714251/1188513), for more robust and much simpler code. — Mathieu Guindon, Nov 12 '18 at 18:34

Dan Donoghue · Accepted Answer · 2018-11-12T23:38:42.843

Use a for next loop and don't select, this should do what you need. Make sure you read this code and understand how it relates back to your original code.

Sub Test()
Dim X As Long
For X = 79 To 1 Step -1 'Step -1 makes it go backwards
    If Range("B" & X).Text = "Original" Then 'Notice I am not actually selecting anything in this code, I don't need to in order to manipulate it
        With Range("B" & X).End(xlToRight)
            For Each Border In .Borders 'Loop the borders so you don't have to name each one
                Border.LineStyle = xlNone
            Next
            With .Borders(xlEdgeTop)
                .LineStyle = xlDot
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThin
            End With
        End With
    End If
Next
End Sub

Need help looping macro

1 Answers1