#VALUE error setting Interior.ColorIndex property

Question

I am trying to loop through the rows in a worksheet looking for a non-zero value in column 11 and then highlight the entire row. I found examples of doing that on this site, but when I attempt to emulate those examples, I get a #VALUE error returning from the function.

Code is as follows:

Function HighlightRows() As Long
    Dim c As Long
    Dim i As Long
    Dim w As Worksheet


    Set w = ThisWorkbook.Worksheets("Sheet1")
    c = 0
    For i = 2 To w.UsedRange.Rows.Count
        If w.Cells(i, 11).Value = 1 Then
            c = c + 1
            w.Rows(i).Interior.ColorIndex = 3
        End If
    Next i

    HighlightRows = c

End Function

When I comment out the code that attempts to set the Interior.ColorIndex property of the code, everything appears to work properly and I get the appropriate count returned by the function (except, of course that I don't get the highlighting).

I have also tried the following:

• w.Cells(i,11).EntireRow.Interior.ColorIndex = 3
• w.Range("B" & i & ":O" & i).Interior.ColorIndex = 3
• w.Range(Cells(i,2),Cells(i,15)).Interior.ColorIndex = 3

What am I missing?

Answer 1

Use a Sub and not a function:

Sub HighlightRows()
    Dim c As Long
    Dim i As Long
    Dim w As Worksheet


    Set w = ThisWorkbook.Worksheets("Sheet1")
    c = 0
    For i = 2 To w.UsedRange.Rows.Count
        If w.Cells(i, 11).Value = 1 Then
            c = c + 1
            w.Rows(i).Interior.ColorIndex = 3
        End If
    Next i

    MsgBox c

End Sub

Answer 2

If there is a will, there is a way:

Function DoHighlight(what As Integer)
    ThisWorkbook.Worksheets("Sheet1").Cells(what, 1).EntireRow.Interior.ColorIndex = 3
End Function

Function HighlightRows() As Long
    Dim c As Long
    Dim i As Long
    Dim w As Worksheet
    Set w = ThisWorkbook.Worksheets("Sheet1")
    c = 0
    For i = 2 To w.UsedRange.Rows.Count
        If w.Cells(i, 11).Value = 1 Then
            c = c + 1
            Evaluate ("DoHighlight(" & i & ")")
        End If
    Next i

    HighlightRows = c

End Function

The point here is to bypass the restriction by evaluating a piece of code from a string that will call a separate function to do the job outside of the formula function.

NOTE: i would recommend against it, altering cells formatting from a formula is disabled for a reason. use at your own risk.