VBA Excel ColorIndex Property

This tutorial explains how to use VBA Excel colorIndex Property to set color and get color from Cell or Shape, meaning of -4142, -4105, xlNone, xlAutomatic

What does VBA Excel ColorIndex Property do?

VBA Excel ColorIndex Property is to set color or get color for Objects like Cell color and Shape color. ColorIndex offers 56 basic colors plus the following special numbers.

ColorIndex Value Explanation
-4142 / xlColorIndexNone / xlNone No fill
-4105 / xlColorIndexAutomatic / xlAutomatic Default color (white)

colorindex_10

From my personal experience, ColorIndex is a better Property compared with Color Property, you always get the color code 16777215 for both white and no fill, but you get different ColorIndex code.

You may also want to compare ColorIndex Property with RGB Property to use a different Property to set color for Cells.

Syntax of VBA Excel ColorIndex Property

expression.colorIndex

Example of VBA Excel ColorIndex Property

Example 1: Set Cell A1 font color to red

Range("A1").Font.ColorIndex = 3

Example 2: Set Cell A1 back color to red

Range("A1").Interior.ColorIndex = 3

Example 3: Set Cell A1 border color to red

Range("A1").Borders.ColorIndex=3

Example 4: Get Cell A1 ColorIndex

col = Range("A1").Interior.ColorIndex

Example – Find the first colored Cell using ColorIndex Property

The below example is copied from Microsoft Community and was answered by me.

Question

In column A I would like to indicate the value of the first coloured cell horizontally.   By colored cell I mean cell with having a “fill color”.  If you need an example then I could give it.

With which formula (or VB macro code) could I achieve that?

Answer

Public Function wFirstColorCell(rng As Range)
    wFirstColorCell = ""
    For Each r In rng
        If r.Interior.ColorIndex <> xlNone Then
            wFirstColorCell = r.Value
            Exit Function
        End If
    Next r
End Function

Remarks

The Op asked how to find the first colored cell in a row, which apparently means the order is from left to right. In case the given range is more than 1 row, the looping order of For…Each loop is from left to right in the same row, and then move to the next row until the end of the Range. To know more about this Function, read the below article

Excel VBA custom Function Find the first colored Cell value

Leave a Reply

Your email address will not be published.