Getting cell Interior Color Fails when range passed from Worksheet function

I am trying to get write a simple function one can call from a cell that would return if the background of a given cell has a specific background color.

This function works as expected when called from a sub-routine, but fails when called from the worksheet. At the line

IntColor = Cell.DisplayFormat.Interior.Color


Here is all of the code

Option Explicit

Public Function GetCellRGB(Rng As Range) As Integer()
Dim Result(1 To 3) As Integer
Dim Cell As Range
Set Cell = Rng.Cells(1, 1)

Dim IntColor As Integer

' when called from worksheet, function exits here with a #VALUE error
IntColor = Cell.DisplayFormat.Interior.Color

Result(1) = IntColor Mod 256 ' red
Result(2) = IntColor 256 Mod 256 ' green
Result(3) = IntColor 65536 Mod 256 ' blue

GetCellRGB = Result
End Function

Public Function IsColor(Rng As Range, R As Integer, G As Integer, B As Integer) As Boolean
Dim Vals() As Integer

Vals = GetCellRGB(Rng)
If R = Vals(1) And G = Vals(2) And B = Vals(3) Then
IsColor = True
Else
IsColor = False
End If
End Function

' This works as expected
Sub ColorTest()
Dim Rng As Range
Set Rng = ThisWorkbook.ActiveSheet.Range("A1")
Debug.Print IsColor(Rng, 255, 0, 0)
End Sub


DisplayFormat

2 Answers
2

Here's a workaround to the "DisplayFormat not available in a UDF" problem.

It uses Evaluate to side-step the UDF context

Evaluate

Public Function DFColor(addr)
DFColor = Range(addr).DisplayFormat.Interior.Color
End Function

Function CFColorMatches(rng As Range, R As Long, G As Long, B As Long)
CFColorMatches = (rng.Parent.Evaluate("DFColor(""" & rng.Address & """)") = RGB(R, G, B))
End Function


Note also you really don't need all that RGB-related code

RGB is calculated by VBA itself and you don't need to assume it is an array, it is a long integer in fact so if you want to check the background color of a cell you can just simply do this which will work on the worksheet too:

Public Function IsColor(Rng As Range, R As Integer, G As Integer, B As Integer) As Boolean
If Rng.Interior.Color = RGB(R, G, B) Then
IsColor = True
Else
IsColor = False
End If
End Function


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.