CODEWITHSUNDEEP
vbaexcel
James John McGuire 'Jahmic'
James John McGuire 'Jahmic'

Reputation: 12684

Excel VBA: Returning the cell value of calculated result

I have the following VBA function:

Function IndexOfColor(InRange As Range, ColorIndex As Long) As Excel.Range
Dim R As Range

Application.Volatile True
If IsValidColorIndex(ColorIndex) = False Then
    IndexOfColor = 0
    Exit Function
End If

For Each R In InRange.Cells
    If R.Interior.ColorIndex = ColorIndex Then
        IndexOfColor = R
        Exit Function
    End If
Next R

IndexOfColor = 0
End Function

In my excel sheet, I call this:

=IndexOfColor(D15:M24,37)

And always get "#VALUE". I've debugged the function, right until the end, and no problems there. While this should just return 1 result (I'm looking at the range, and there is only one colored cell), I've also tried making this an array result (CTRL-SHIFT-ENTER).

Suggestions?

Upvotes: 0

Views: 2505

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Is this what you are trying? If the cells will not have decimals then you can replace Variant with Long

Function IndexOfColor(InRange As Range, ColorIndex As Long) As Variant
    Dim R As Range

    IndexOfColor = 0

    On Error GoTo Whoa

    Application.Volatile True

    If Not IsValidColorIndex(ColorIndex) = False Then
        For Each R In InRange.Cells
            If R.Interior.ColorIndex = ColorIndex Then
                IndexOfColor = R.Value
                Exit For
            End If
        Next R
    End If
Whoa:
End Function

Upvotes: 1

Related Questions