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 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 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. 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

wFirstColorCell(rng)

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.

first_color_cell

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

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *