VBA code returning #value error

Question

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

Unrelated, but your signature would be much more explicit if it were Public Function CheckColour(ByVal target As Range) As String - right now it's implicitly public, range is implicitly ByRef and Variant, and the function implicitly returns a Variant. — Mathieu Guindon
– Mathieu Guindon, Commented Feb 7, 2017 at 16:10

Alex K. · Accepted Answer · 2017-02-07 15:58:34Z

6

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

answered Feb 7, 2017 at 15:58

Alex K.

177k32 gold badges275 silver badges298 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hk96fh Over a year ago

Thanks Alex, so is there any way I can take the conditional formatting colour and display it as a word, say red, green etc. ?

Tim Williams · Accepted Answer · 2017-02-08 20:39:02Z

0

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

edited Feb 8, 2017 at 20:39

answered Feb 7, 2017 at 18:02

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

VBA code returning #value error

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related