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 business analysis, project management, and also 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 in Access / Excel
- Microsoft Specialist in MS Project
- 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 *