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

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

Hi Rem, I have updated the post to show you how

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?

Hi Akane

=wFirstColorCellAddress(A2:G2) << this should work Can you describe how it doesn't work? or you can send me the workbook at scammera1@yahoo.com.hk