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