VBA function cannot read color of Excel cell

Question

Hi,

I have created this simple VBA function:

Function GetCellColor(myCell As Range)
    Dim colorIndex As Integer
    colorIndex = myCell.Interior.ColorIndex
    GetCellColor = colorIndex
End Function

It's just supposed to return the Color Index of a cell. However, I always get -4142 which is the value for white aka nothing. I also encounter this problem when I try Color instead of ColorIndex, I get 16777215 then. I've tested it on different machines with different files, but nothing seems to work. Any ideas?

Answer 1

I think that the result is correct, because -4142 means xlColorIndexNone (https://learn.microsoft.com/en-us/office/vba/api/excel.xlcolorindex), i.e. no specific colour is assigned yet. The myCell.Interior.Color returns 16777215, or FFFFFF using Hex(myCell.Interior.Color), which denotes the white colour. In case of blue background, for example, you should get FF0000 (FF is Blue, 00 is Green, 00 is Red).

Make sure that you are checking the right range.

To get the text colour, try myCell.Font.Color.