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 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.
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:
Download a Sample Workbook
There are several examples in the workbook you can download :
Enter your email address below to get the 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: