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.

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

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

 

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

4 thoughts on “Excel VBA custom Function Find the first colored Cell value

  1. How would you go about doing this if you wanted the cell’s location/address and not the value? “C2” in the first example.

  2. Thank you for this example! it’s what I’ve been looking for.

    >Find the first color cell Address
    However this doesn’t seem to work for me. ( Along with the “Find the first colored Cell value using ColorIndex Property”)

    I put the wFirstColorCellAddress code in the VBA, then added the formula to a cell. (=wFirstColorCellAddress(A2:G2)) Is this not correct?

Leave a Reply

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