Return a Range from a UDF

I've previously written a UDF to count colored cells and then perform various maths functions on the values in those cells, like SUM, AVERAGE or COUNT.

In this post I'm going to rewrite that code so that we can return a range from a UDF. This range contains all the cells that match the reference color, and you can then use this range in other functions and calculations.

I had already planned to do this, but following a comment on the last post, Catalin (our support guru) posted his code which does the same job, so some of the credit must go to him too.

The UDF

The UDF is called FindColors and is really a pretty straightforward few lines of VBA:

'
' Written by Philip Treacy
' myonlinetraininghub.com/return-a-range-from-a-udf
'

Function FindColors(InputRange As Range, ReferenceCell As Range) As Range
    
    Dim ReferenceColor As Long
    Dim Cell As Range, Result As Range
     
    ReferenceColor = ReferenceCell.Interior.Color
        
    For Each Cell In InputRange

        If Cell.Interior.Color = ReferenceColor Then
            
            If Result Is Nothing Then
                
                Set Result = Cell
                
            Else

                Set Result = Union(Result, Cell)

            End If

        End If

    Next Cell
    
    Set FindColors = Result

End Function

All we need to do is check each cell in the InputRange sent to the function, to see if that cell has the same background color as our ReferenceCell.

A regular old For ... Next loop does this job for us.

UNION

If we find cells that match the color of our ReferenceCell, we use the UNION operator to join these separate cells together into a single range.

Set Result = Union(Result, Cell)

Returning the Resulting Range

The last thing to do is return the result of the function, which is the range of colored cells matching our ReferenceCell.

But, because we are returning a range object we must Set the function result like so:

Set FindColors = Result

Using the UDF Result

If you want to find the SUM of all cells colored the same as B9, and where ColoredCells is a named range, you'd enter this in a cell:

=SUM(FindColors(ColoredCells,B9))

Download a Sample Workbook

There are several examples in the workbook you can download :

Download the Excel Workbook.

A Word on Recalculation

If you change the background color of the cell using the Ribbon (or a couple of other ways), the function output is not recalculated. That's just the way Excel works and I wrote about this in a previous post - check the section headed Recalculation.

Bonus Function - GetColor

This was in the previous workbook too, but I didn't mention it.

Use this function to get the hex value of a cell's background color. Or to check that certain cells all have the same color.

To use it just call the function from a cell:

=GetColor(B9)

Check Out Other Posts on User Defined Functions

Creating a UDF

Creating Multi-Function UDF's

Creating a Reference to PERSONAL.XLSB

Creating an Excel Add-In For UDF's

Count, Sum and Average Colored Cells