Excel VBA custom Function Find the first colored Cell value

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

Excel VBA Function sum colored Cell/ count colored Cell

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 Excel ColorIndex Property

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

to

Interior.ColorIndex = color_index (click here to see the list of color index)

Function Syntax – Find the first colored Cell value using ColorIndex Property

wFirstColorCell(rng)

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.

Formula	Result	Explanation
=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