Get ColorIndex Number for Cell in a Range

  • Thread starter Thread starter Ronster
  • Start date Start date
R

Ronster

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Blinking cells 6
Run - time error '1004' 1
Find First Cell With a Font ColorIndex =3 10
Run Error while protected 1
Border formatting row of cells 22
Protection in VBA. 2
Arrays and scattered cell values or another way? 16
keep getting the error message - Compile error: Else without If 2

Back
Top