1

I've written a function that essentially returns a weighted score by assigning values of 1 and 2 to different conditionally formatted cells. When I call this function in the worksheet, it returns the #VALUE! error.

Function ColorWeight(Factors As Range) As Integer
Dim x As Range
Dim score As Integer

score = 0
For Each x In Factors
    If x.DisplayFormat.Interior.color = Range("C5").Interior.color Then
        score = score + Cells(14, x.Column).Value * 2
    ElseIf x.DisplayFormat.Interior.color = Range("C9").Interior.color Then
        score = score + Cells(14, x.Column).Value * 1
    End If
Next
ColorWeight = score

End Function

However, when I run this code as a sub and set the range to a specific range, as below, it works fine.

Sub ColorWeight()
Dim Factors As Range
Dim x As Range
Dim score As Integer

Set Factors = Range("G17:Q17")
score = 0
For Each x In Factors
    If x.DisplayFormat.Interior.color = Range("C5").Interior.color Then
        score = score + Cells(14, x.Column).Value * 2
    ElseIf x.DisplayFormat.Interior.color = Range("C9").Interior.color Then
        score = score + Cells(14, x.Column).Value * 1
    End If
Next
Debug.Print score
End Sub

What is the difference that I'm missing that makes the function not work?

Gregg Jossie
  • 31
  • 3
  • 3
    You can't access `DisplayFormat` in a UDF called from a cell. – Rory Oct 25 '17 at 15:53
  • Try with `Public Function` – Dani Aya Oct 25 '17 at 15:59
  • https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-displayformat-property-excel – Tim Williams Oct 25 '17 at 16:04
  • @Jeeped - I don't think this is a duplicate: the OP already knows about DisplayFormat. This is a question specifically about using DisplayFormat in a UDF – Tim Williams Oct 25 '17 at 16:11
  • Yeah, my note on that in my referenced answer is only a footnote but it is there and was the first one I found although I know I've stated the same thing several times. I'll reopen. –  Oct 25 '17 at 16:15
  • @Jeeped - yes I missed your note (and I see similar notes from you on related questions) Might be worth its own topic though. – Tim Williams Oct 25 '17 at 16:22

1 Answers1

4

Here's a basic example of a work-around:

Function GetDFColor(shtName, cellAddress)
    GetDFColor = ThisWorkbook.Sheets(shtName).Range(cellAddress). _
                                          DisplayFormat.Interior.Color
End Function


'works when called as a UDF
Function DisplayFormatColor(rng As Range)
    DisplayFormatColor = Application.Evaluate("GetDFColor(""" & _
                         rng.Parent.Name & """,""" & rng.Address() & """)")
End Function
Tim Williams
  • 154,628
  • 8
  • 97
  • 125