Get ColorIndex Number for Cell in a Range

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

.

Relevant Pages

  • Re: a macro for conditional sum based on cell color index
    ... Sorry for my poor programming skills and for asking such basic ... Function CFmt(RangeInQuotes, ColorIndex) ... Dim ACell As Range ... For Each Cell In ACell ...
    (microsoft.public.excel.programming)
  • Re: a macro for conditional sum based on cell color index
    ... Function CFmt(RangeInQuotes, ColorIndex) ... Dim ACell As Range ... For Each Cell In ACell ... you can sum the cells in A1:A10 that have red ...
    (microsoft.public.excel.programming)
  • RE: Vlookup help please
    ... 'I think that the last cell should be named here ... .Weight = xlHairline ... .ColorIndex = xlAutomatic ...
    (microsoft.public.excel.programming)
  • Re: Criteria Based on Formatting in COUNTIF
    ... > Counting coloured cells, either cell colour or font colour, is easily ... > function has been specifically designed to return an array of colorindex ... > Dim cell As Range, ... > "Syed Zeeshan Haider" wrote in message ...
    (microsoft.public.excel.misc)
  • Re: selection.font.color returns wrong color; the first execution
    ... If .ColorIndex < 0 Then ... To find the Font color used in cell "A1" use: ... I normally define and set worksheet and workbook objects then use those ... notice that the Font Color was reported as 255 ...
    (microsoft.public.excel.programming)