This tutorial explains how to create an Excel VBA custom Function to find the first colored Cell value in a Range using ColorIndex Property.
You may also want to read
Find the first colored Cell value using ColorIndex Property
This custom Function was originally created for a question raised in Microsoft Community and was answered by me. It seems that many people have been asking similar questions like finding the first non-empty Cell, finding the first cell that contains formula, etc, so I am moving my Function to this blog.
You are recommended to read the below article before using this custom Function.
VBA Function Code – Find the first colored Cell value using ColorIndex Property
In the below Function, I use ColorIndex<>xlNone to identify colored Cell. White is deemed as colored, while “No Fill” is not colored.
If you just want to sum or count specific color, change the code
Interior.ColorIndex <> xlNone to Interior.ColorIndex = color_index (click here to see the list of color index)
Public Function wFirstColorCell(rng As Range) Application.Volatile wFirstColorCell = "" For Each r In rng If r.Interior.ColorIndex <> xlNone Then wFirstColorCell = r.Value Exit Function End If Next r End Function
Function Syntax – Find the first colored Cell value using ColorIndex Property
rng is a Range that contains multiple Cells, in which you want to find the first colored Cell.
For Range that contains 1 row, “first colored Cell” is defined as the most left colored Cell
For Range that contains more than 1 row, “first colored Cell” is defined as the most left colored Cell scanning in the first row, if not found, continue to scan the 2nd row.
Function Example – Find the first colored Cell value using ColorIndex Property
Once you type a formula in Cell, the result cannot be refreshed automatically if you add/remove color to argument Range.
|=wFirstColorCell(A2:C2)||3||The first colored is the left most colored Cell in a row|
|=wFirstColorCell(A2:A8)||1||The first colored is the top most colored Cell in a column|
|=wFirstColorCell(A3:C8)||8||The first colored is the top most and left most colored Cell|