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. 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 my another post 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. It is important to note that White is deemed as colored, while “No Fill” is not colored.
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
If you just want to sum or count specific color, change the code from
Interior.ColorIndex <> xlNone
Interior.ColorIndex = color_index (click here to see the list of color index)
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 a selected Range, For Loop loops each Cell from left column to right and then loop the next row, therefore the first cell is defined in this logic.
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|
Find the first color cell Address
Instead of getting the first colored cell value, you can modify a little to get the cell address (such as A1, C10).
Public Function wFirstColorCellAddress(rng As Range) Application.Volatile wFirstColorCell = "" For Each r In rng If r.Interior.ColorIndex <> xlNone Then wFirstColorCell = Replace(r.Address, "$", "") Exit Function End If Next r End Function