CODEWITHSUNDEEP
vbaexcel
hk96fh
hk96fh

Reputation: 21

VBA code returning #value error

I'm trying to display the colour from the conditional formatting in excel. In excel I simply use =CheckColour(B5) for example, and when I hit return it works. However, when I ask the sheet to calculate, the function gives #VALUE! and I don't know where I've gone wrong. Any help is appreciated as I'm a beginner at VBA. Thanks

    Function CheckColour(range)
        If range.DisplayFormat.Interior.Color = RGB(255, 0, 0) Then
        CheckColour = "Red"
        ElseIf range.DisplayFormat.Interior.Color = RGB(0, 130, 59) Then
        CheckColour = "Green"
        Else
        CheckColour = "Amber"
        End If
    End Function

Upvotes: 2

Views: 687

Answers (2)

Tim Williams
Tim Williams

Reputation: 166306

This (simplified example) seems to work for me:

Public Function CheckColour(src As Range)
    Application.Volatile
    CheckColour = src.Parent.Evaluate("GetColor(" & src.Address(False, False) & ")")
End Function

Public Function GetColor(src As Range)
    GetColor = src.DisplayFormat.Interior.Color
End Function

Upvotes: 0

Alex K.
Alex K.

Reputation: 175766

Yep, because:

Note that the DisplayFormat property does not work in user defined functions. For example, in a worksheet function that returns the interior color of a cell, you use a line similar to: Range(n).DisplayFormat.Interior.ColorIndex. When the worksheet function executes, it returns a #VALUE! error. Ref.

Instead use:

range.Interior.Color

Upvotes: 6

Related Questions