VBA iteration loop

Question

I need to highlight cells in each row that are in excess of the value in Column AU for that row of data; then do this for the next row, but I cannot get the value of column AU to be iterative meaning changing to the next row's value.

Let me know what I'm doing wrong here.

Sub highlight()
    Application.ScreenUpdating = False

    Dim myRange As Range
    Dim i As Long, j As Long

    Set myRange = Range("F11:AP20")
    For n = 11 To 20
        For i = 1 To myRange.Rows.Count
            For j = 1 To myRange.Columns.Count
                If myRange.Cells(i, j).Value < Range(AUn).Value Then
                    myRange.Cells(i, j).Interior.Color = 65535
                Else
                    myRange.Cells(i, j).Interior.ColorIndex = 2
                End If
            Next j
        Next i
    Next n
End Sub

Forgive me if this is a noob question, but I don't use Range() that much (I ALWAYS loop on the cells directly), but what is "AUn"? Is that just an undefined variable that contains zero? — Maury Markowitz, Jun 04 '15 at 18:23
Is this what you are trying? `If myRange.Cells(i, j).Value < Range("AU" & n).Value Then` — Siddharth Rout, Jun 04 '15 at 18:34
AUn is my attempt to call the column as an array of sorts by allowing me to cycle through the values as n changes as if I use the function (=$AU11, rather than =$AU$11) — gduhoffmann, Jun 04 '15 at 19:21

score 1 · Accepted Answer · answered Jun 04 '15 at 18:45

1

You have 1 loop extra
AUn is being treated as a blank variable. I guess you want to work with relevant row in Col AU.

Is this what you are trying? (Untested)

I have commented the code so let me know if you still have any questions :)

Sub highlight()
    Application.ScreenUpdating = False

    Dim myRange As Range
    Dim n As Long, j As Long
    Dim ws As Worksheet

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Set your range
        Set myRange = .Range("F11:AP20")

        '~~> Loop through the rows (11 to 20 in this case)
        For n = myRange.Row To (myRange.Row + myRange.Rows.Count - 1)
            '~~> Loop through the columns (F to AP in this case)
            For j = myRange.Column To (myRange.Column + myRange.Columns.Count - 1)
                If .Cells(n, j).Value < .Range("AU" & n).Value Then
                    .Cells(n, j).Interior.Color = 65535
                Else
                    .Cells(n, j).Interior.ColorIndex = 2
                End If
            Next j
        Next n
    End With
End Sub

answered Jun 04 '15 at 18:45

Siddharth Rout

147,039
17
206
250

okay tried this code which doesn't seem to recognize the value in AU (The value stems from a formula (=AQ+(AR*2))) will that cause the cell reference to be zero? – gduhoffmann Jun 04 '15 at 19:19
Did you use `.Range("AU" & n).Value` and not `Range("AU" & n).Value`? Notice the DOT before Range Object? – Siddharth Rout Jun 04 '15 at 19:22
Thanks, disregard that last comment as I was referencing AU when I should have been using AT Thanks again perfect code! – gduhoffmann Jun 04 '15 at 19:30
just add this minor change to make this good for and workbook num = ActiveSheet.Columns("AP").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row With ws '~~> Set your range Set myRange = .Range("F11:AP" & num) – gduhoffmann Jun 04 '15 at 19:43
That is ok. That is not required as it is not part of your original query. Also do not use `.Find` unless you are sure that the sheet has data. You may want to see [This](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) – Siddharth Rout Jun 04 '15 at 19:45
Thank you! Your answers and suggestions are perfect you're completely right, my original post didn't mention the general use and application need, but your answer was spot on! – gduhoffmann Jun 04 '15 at 19:49

VBA iteration loop

1 Answers1