Get ColorIndex Number for Cell in a Range
 

Is there a way to directly read in the colorindex of cell into an
array? As you can see by the code below, I can get the value ok but
not the colorindex value. I know I could probably do a "For Each Cell
in Range("A1:C5")" type of command and get the colorindex number that
way but I thought the code below should work. Any ideas?

Sub GetCellValueAndColor()

' cell A1 contains the number 123 and the font color is Red
' ColorIndex number for Red is 3

Dim ArrayCellValue As Variant, ArrayCellColor As Variant

ArrayCellValue = Range("A1:C5").Value
ArrayCellColor = Range("A1:C5").Font.ColorIndex

MsgBox ArrayCellValue(1, 1) ' displays 123

' display A1 Only for test
MsgBox Range("A1").Font.ColorIndex ' displays 3

MsgBox ArrayCellColor(1, 1) 'Type mismatch
MsgBox Str(ArrayCellColor(1, 1)) 'Type mismatch
MsgBox Val(ArrayCellColor(1, 1)) 'Type mismatch
MsgBox CStr(ArrayCellColor(1, 1)) 'Type mismatch

End Sub

Get ColorIndex Number for Cell in a Range
 

see http://www.cpearson.com/excel/colors.htm

"Ronster" wrote in message
Is there a way to directly read in the colorindex of cell into an
array? As you can see by the code below, I can get the value ok but
not the colorindex value.