CODEWITHSUNDEEP
excelvbafor-loop
Felix T.
Felix T.

Reputation: 530

Using for loops to identify rows

I tried here, here, and here.

I'm trying to highlight a row based on the string contents of a cell in the first column.

For example, if a cell in the first column contains the string "Total", then highlight the row a darker color.

Sub tryrow()
    Dim Years
    Dim rownum As String
    
    Years = Array("2007", "2008", "2009") ' short example 
    For i = 0 To UBound(Years)
        Set rownum = Range("A:A").Find(Years(i) & " Total", LookIn:=xlValues).Address
        Range(rownum, Range(rownum).End(xlToRight)).Interior.ColorIndex = 1
    Next i
End Sub

I get this error message:

Compile error: Object required

The editor highlights rownum = , as if this object hadn't been initialized with Dim rownum As String.

Upvotes: 0

Views: 85

Answers (2)

JohnyL
JohnyL

Reputation: 7142

You can avoid loop by using autofilter which will work much faster. The code assumes that table starts from A1 cell:

Sub HighlightRows()

    Dim rng As Range, rngData As Range, rngVisible As Range
    '//All table
    Set rng = Range("A1").CurrentRegion
    '//Table without header
    With rng
        Set rngData = .Offset(1).Resize(.Rows.Count - 1)
    End With
    rng.AutoFilter Field:=1, Criteria1:="*Total*"
    '// Need error handling 'cause if there are no values, error will occur
    On Error Resume Next
    Set rngVisible = rngData.SpecialCells(xlCellTypeVisible)
    If Err = 0 Then
        rngVisible.EntireRow.Interior.ColorIndex = 1
    End If
    On Error GoTo 0

End Sub

Upvotes: 0

Josh Eller
Josh Eller

Reputation: 2065

You've got a couple issues here, indicated below alongside the fix:

Sub tryrow()
    Dim Years() As String 'Best practice is to dim all variables with types. This makes catching errors early much easier
    Dim rownum As Range 'Find function returns a range, not a string

    Years = Array("2007", "2008", "2009") ' short example
    For i = 0 To UBound(Years)
        Set rownum = Range("A:A").Find(Years(i) & " Total", LookIn:=xlValues) 'Return the actual range, not just the address of the range (which is a string)
        If Not rownum Is Nothing Then 'Make sure an actual value was found
            rownum.EntireRow.Interior.ColorIndex = 15 'Instead of trying to build row range, just use the built-in EntireRow function. Also, ColorIndex for gray is 15 (1 is black, which makes it unreadable)
        End If
    Next i
End Sub

Upvotes: 2

Related Questions